Timesheet

[Ian]

Could you help me out please. I'm trying to set up a sheet to calculate hours worked, less Core Time, and + or - the differences in hours/minutes. An example is -

Start End Start End Total hours worked core time 08:15 12:10 12:45 16:55 08:05 7.24
Difference between Core and Total (+ -)

I just cannot work out how to calulate the final part - the differences - so that when the total hours worked is calculated, the difference (+ or -) will be calculated. Is this not possible? I'm trying to do it all on the same Row. Could there be another way of doing it? I'd appreciate your help.
Thanks
Ian

 

[Biff]

Hi Ian!

Need a better explanation?

What is 7.24? Hours?

8:05 minus 7.24 ?

What exactly do you want?

Biff

-----Original Message-----
Could you help me out please. I'm trying to set up a

sheet to calculate hours worked, less Core Time, and + or -
the differences in hours/minutes. An example is -

Start End Start End Total hours

worked core time 08:15 12:10 12:45
16:55 08:05 7.24

Difference between Core and Total (+ -)

I just cannot work out how to calulate the final part -

the differences - so that when the total hours worked is
calculated, the difference (+ or -) will be calculated.
Is this not possible? I'm trying to do it all on the same
Row. Could there be another way of doing it? I'd
appreciate your help.

 

[Ian]

Hi Biff

The times are hours and minutes, eg 7 hrs 24 minutes.
I want to be able to set a timesheet, detailing the time started work eg
0815, finish for lunch at 1230, restart work at 1315 and end the day at 1730
(all times are 'dreams' as I often don't finish till much later - however
that's another story). Then take away the Core daily work time of 7 hrs 24
minutes which leaves a positive or negative figure. This is the amount of
Flexi time due me.
But I would like to be able to have the calculation determine the amount of
Flexi time.
Is this any clearer?

Apologies for the ramblings.

Ian

 

[Dave Palmer]

Hi Ian

It does appear that several of us all seem to be getting
to the same stage here. I asked the same question
yesterday (see Calculating Times Aug 5th) and got a good
answer from Jerry W Lewis about how to display negative
time. We use the time in brackets eg (1:30) to indicate
that someone has not done a full day. This works fine, but
now I am trying to work out how I can total that column to
derive total time over or under for the month.

I can send you a copy of my master template if it would
help, but you may want to wait to see if I can get it
perfect.

Regards

Dave

 

[Ian]

Hi Dave

Thanks for the info. I'm new to Excel and these functions, but it does seem
particularly dificult to complete what I thought to be a simple task.

Yes, if you could send me a copy of your master template that would be a
great help in these early stages. A copy of the finished job would also be
great.

Thanks
Ian

 

[Biff]

Hi Ian!

Yes, that's much clearer.

But here's the problem I see. 7.24 is a decimal value and
is not the same as 7 hrs 24 mins. So, to get the correct
value in h:mm:

A1 = start
B1 = end
C1 = start
D1 = end
Core time = 7:24

=(B1-A1)+(D1-C1)-TIME(7,24,0)

Format as [hh]:mm

Now, that fromula doesn't account for any time that spans
past midnight. For example, if you worked from 11:00 PM to
7:00 AM. If you don't work those type of hours then
nothing more is needed. If you do work those hours, then:

=(B1-A1+(A1>B1)+(D1-C1+(C1>D1)-TIME(7,24,0)

Now this leaves the possibility that your total hrs worked
may be less than the core time.

XL cannot display negative times when using date/time
formats. It will display ##########. Dates and times are
stored as decimal values. The value you see as 11:00 AM is
done by formatting but the underlying actual value
is .45833333.

So, in order to show a negative time while still retaining
a date/time format you can return the absolute value of
the calculation then conditionally format the cell to show
a negative value:

=ABS((B1-A1)+(D1-C1)-TIME(7,24,0))

Then select the cell and apply conditional formatting such
that if the cell value is less than zero then format with,
for example, a bold red font.

To conditional format:

Select the cell.
Goto Conditional Formatting
Formula is: =(B1-A1)+(D1-C1)-TIME(7,24,0)<0
Select your desired formats.
OK out

Hope that helps!

Biff

 

[Dave Palmer]

Hi Ian

I am new to these forums, and not sure how I email you
direct (attaching the timesheet master)or how you all get
an email address of (e-mail address removed).

Regards


Dave

 

[Ian]

Dave and Bif

I've posted this msg twice so that you, hopefully, will be able to read it.

Problem sorted. Got a template working which does the calculations I need,
using a combination of the information you both gave.
It now calculates the total hours/mins per day, less core time, totals the
weekly and monthly hours/mins andcalculates flexi time as a credit. A manual
adjustment has to be made if theere is a negative element to flexitime. Not
a major problem though.

As for emailing - I don't think there is a way of providing this address
other than on a 'posting'. If there is a way without posting an address
here, let me know. I can always use one and then dump it once the spam
starts rolling in.

Thanks for your help.

Ian

 

[Dave palmer]

Hi Ian

Glad to hear you have sorted your timesheet out. To get
over the negative time adding up, I changed to a debit and
credit column, and so the columns add up OK. The boxes
just show whichever is applicable and no-one has to
calculate anything. It also lets the user see how the
month is progressing in relation to Toil Time.

If you still wish for me to send you a copy, perhaps you
could ring and give me your details.

Dave
0116 2729120