Tricky hour calculation (re overtime spreadsheet).

[StargateFan]

I realize that if I just put the hours worked myself in the
spreadsheet I just created this week, i.e., did the calculation myself
and just typed in the actual hours worked, that that would avoid the
problem I'm running into. However, it would be just so much easier
and so neat to type in the actual start and end times involved and to
then let Excel figure out what the overtime is if there is indeed
overtime <g>. That would be too kewl for words! <g>

I did up an overtime logger/calculation spreadsheet. It worked fine
as is until today; today I didn't work a full day so the calculations
were useless for this date.

I yielded results on a massive search today on the net. The formula
below is what I was able to come up with and I have this in cell E2
which subtracts 7.5 hours from D2 (corresponding to regular hours
worked so must not be included in overtime calculation):

=IF(D2<>"",SUM(D2-TIME(7,30,0)),"")

D2 shows 7h30m (calculated by end time C2 - start time B2 less 30
minutes for lunch) and E2 shows 0h0m because the above formula
subtracts 7 hours, 30 minutes to account for regular work hours vs
overtime, as mentioned above.

Up till here, so far so good.

If I do actually work 7.5 hours, then, I get this 0h0m display.
But if I didn't work 7.5 hours, like today, the above doesn't work.

I had an appointment today and so I actually only worked 4.5 hours.
D5, then, shows 4h33m instead of 7h30m.
But E5 shows #### where it should show -2h57m
(because I didn't work nearly 3 hours due to my appointment).

I've tried displaying the end results differently through cell
formatting, but nothing works.

The goal is to just put the time started in the morning and then my
usual time out and then to put the time I left. So by entering 3
different times only, and without taking out a calculator, when I left
at 6:30 p.m. tonight, the spreadsheet should determine that on this
day I didn't work overtime and that I am still actually 1 hour short
or so.

I know this is tough to understand by description, but hope the
concept itself is understood even if not the above.

So, here is the problem again in a nutshell:

How to get the above #### to display the time correctly when there is
a negative number of hours to show, i.e., in this case -2h57m
yet keep 0h0m when I did work the full day.




(I use custom time display of: h"h"m"m" to get our French Canadian
24 hours display which is easiest for all to understand as that's what
we use in our dept.)

Thanks in advance!

 

[Ron Rosenfeld]

So, here is the problem again in a nutshell:

How to get the above #### to display the time correctly when there is
a negative number of hours to show, i.e., in this case -2h57m
yet keep 0h0m when I did work the full day.

If you want to display negative time in the Excel time format, you will need to
be using the 1904 date system. Select Tools/Options/Calculations Workbook
Options and SELECT the 1904 date system.

When you switch to the 1904 system, be aware that any dates in your workbook
will be shifted by four years.


--ron

 

[StargateFan]

If you want to display negative time in the Excel time format, you will need to
be using the 1904 date system. Select Tools/Options/Calculations Workbook
Options and SELECT the 1904 date system.

When you switch to the 1904 system, be aware that any dates in your workbook
will be shifted by four years.

Wow! That is sooo amazing! I saw this 1904 reference on the pages
related to date and time calculations
(http://www.cpearson.com/excel/datetime.htm) where I figured out the
formula for subtracting minutes from the times added together but I
didn't understand what that was all about and since I was also dealing
with time, didn't know that a date reference like this was relevant.
Wow.
The pages where I got help on subtracting time is this one:
http://www.cpearson.com/excel/datearith.htm

The 1904 system mentioned here with a bit of an explanation:
http://www.cpearson.com/excel/datetime.htm
(under title "The Two Date Systems Of Excel").

At any rate, the instant I changed the workbook to this 1904 system,
all the hours displayed correctly! So neat. And even the total hours
overtime worked have the correct time calculated. i.e., yesterday,
though I worked till 7 p.m. there wasn't any overtime as I still owed
1 hour, yet the formula took that into account and deducted the 1 hour
out fo the total actual overtime worked.

Thanks so much! The dates I put in do indeed show 2009 instead of
2005 but I'll figure out how to subtract 4 years from the display.

 

[StargateFan]

On Thu, 07 Jul 2005 21:30:00 -0400, StargateFan

[snip]

When you switch to the 1904 system, be aware that any dates in your workbook
will be shifted by four years.

[snip]

Thanks so much! The dates I put in do indeed show 2009 instead of
2005 but I'll figure out how to subtract 4 years from the display.

<lol> Duh, it changed the dates I'd typed in to 2009 when I intially
changed the workbook to the 1904 date system but all I had to do was
just type in 2005 again! <g> I tested the rest of the sheet manually
by adding in other dates as per normal but the correct date is still
displayed. So everything's okay.

I'm guessing that what happens here is that if any date calculations
are done in formulas anywhere else from here on in, that the dates may
be off by 4 years (?). In my case, there are no date calculations at
all, just time ones, so just making sure the dates displayed are
always of the current year should be enough.

Thanks! D

 

[Ron Rosenfeld]

On Thu, 07 Jul 2005 21:30:00 -0400, StargateFan

[snip]

When you switch to the 1904 system, be aware that any dates in your workbook
will be shifted by four years.

[snip]

Thanks so much! The dates I put in do indeed show 2009 instead of
2005 but I'll figure out how to subtract 4 years from the display.

<lol> Duh, it changed the dates I'd typed in to 2009 when I intially
changed the workbook to the 1904 date system but all I had to do was
just type in 2005 again! <g> I tested the rest of the sheet manually
by adding in other dates as per normal but the correct date is still
displayed. So everything's okay.

I'm guessing that what happens here is that if any date calculations
are done in formulas anywhere else from here on in, that the dates may
be off by 4 years (?). In my case, there are no date calculations at
all, just time ones, so just making sure the dates displayed are
always of the current year should be enough.

Thanks! D

Excel stores dates as serial numbers. In the 1900 date system, day 1 is 1 Jan
1900. In the 1904 date system, day 1 is 2 Jan 1904 (day 0 is 1 Jan 1904).

So when you change systems, the serial number does not change -- that is why
you see the 4 year (and 1 day) jump in dates.

When you enter a new date, the proper serial number for that date gets entered.

When you typed in the 2005 date, Excel assigned a serial number that differed
by 1462 from the original.

HTH and glad you've got things working OK for you.


--ron

 

[StargateFan]

On Thu, 07 Jul 2005 21:45:35 -0400, Ron Rosenfeld

On Thu, 07 Jul 2005 21:30:00 -0400, StargateFan

[snip]

When you switch to the 1904 system, be aware that any dates in your workbook
will be shifted by four years.
[snip]

Thanks so much! The dates I put in do indeed show 2009 instead of
2005 but I'll figure out how to subtract 4 years from the display.

<lol> Duh, it changed the dates I'd typed in to 2009 when I intially
changed the workbook to the 1904 date system but all I had to do was
just type in 2005 again! <g> I tested the rest of the sheet manually
by adding in other dates as per normal but the correct date is still
displayed. So everything's okay.

I'm guessing that what happens here is that if any date calculations
are done in formulas anywhere else from here on in, that the dates may
be off by 4 years (?). In my case, there are no date calculations at
all, just time ones, so just making sure the dates displayed are
always of the current year should be enough.

Thanks! D

Excel stores dates as serial numbers. In the 1900 date system, day 1 is 1 Jan
1900. In the 1904 date system, day 1 is 2 Jan 1904 (day 0 is 1 Jan 1904).

So when you change systems, the serial number does not change -- that is why
you see the 4 year (and 1 day) jump in dates.

When you enter a new date, the proper serial number for that date gets entered.

When you typed in the 2005 date, Excel assigned a serial number that differed
by 1462 from the original.

HTH and glad you've got things working OK for you.

Understood. Thankfully that doesn't affect this spreadsheet and it is
working marvellously. I just enter my 3 times each day into the
spreadsheet and it calculates the real overtime.

Thanks.