Calculate Add'l Hours on Timesheet per day

**FMGSCG** · 04-28-2010, 04:11 PM

I have a timesheet that was created years ago and I want to update it to calculate the additional hours rather than manual entry, yet I have never worked with time calculations.

Each person inputs their In & Out time manually. I would like the Add'l Hours cell to calculate anything greater than 7.5 hours (my office works 37.5/wk & 7.5/day) within the Time In & Time Out entries.

The Hours Accumulated cell will total up all the Add'l Hours cells within one week.
The Hours Remaining cell will take the Accumulated Hours and subtract the Hrs Taken within one week.

I can't seem to get this to work. The example I have in the attached spreadsheet is:

Time In: 7:00 (formatted h:mm)
Time Out: 19:00 (formatted h:mm)
Add'l Hours: =(D17-C17)-7.5 (formatted h:mm) -- I get ##### in the cell and don't understand why?

Want the Add'l Hours to calculate as such: =12:00-7:30 = 4.5 or 4:30 or 4 hours 30 mins (not sure which way I want it displayed yet)

Any ideas?

**Shampoo Monkey** · 04-28-2010, 04:35 PM

Hey FMGSCG,

The way time is treated in excel is different than just hours.minutes. It is actually going to be based on a 24 hour day. So, if you want to subtract 7.5 hours, you need to divide 7.5/24 to figure out how the correct excel time. Should be 0.3125.

To summarize, your formula should be:

Please Login or Register  to view this content.

That will produce the 4:30 you are looking for.

**FMGSCG** · 04-29-2010, 09:58 AM

Excellent - that works! In Add'l Hours, I also created an IF statement in case the hours worked are less than 7.5 hrs.

As stated before, the Hours Remaining cell will take the Accumulated Hours and subtract the Hrs Taken within one week. All cells are in h:mm format. The Hours Taken cell is a manual entry and I can't simply type '3' here and receive the correct Hours Remaining. If I change Hours Taken to a number format, it won't calculate correctly. Do I need to divide by 24 again? Okay, that worked -- formula becomes =(Accumulated Hours-(Hours Taken/24)).

I am running into a formatting problem though with the manual entry of Hours Taken (highlighted yellow in attached). I have it formatted as Number with no decimals, yet when I type '3' it enters it as '0.03' and throws off my calculations. How do I make it so I don't have to type '3.' for three hours??

Thanks so much for your help - am learning alot!!

**FMGSCG** · 04-29-2010, 12:29 PM

The previous problem is still here - the formatting of the Hours Taken - and it seems I might be running into the same problem with the Lunch column I added.

Every resource I can find online has people input lunch time in and out, but I don't want to be that detailed. Our policy is that you work 7.5 hours a day regardless of how long your lunch is, yet people are going to clock in their hours including the lunch time and then I want to subtract the lunch time to find the add'l hours worked.

I've been playing with this for hours and can't figure it out. This is what I have:

Time In: 7:45 (h:mm format)
Time Out: 16:00 (h:mm format)
Lunch: 45 mins (number, two decimals format) so appears as .45 (manual entry)
Add'l Hours: Formula I have is: IF(((Time Out-Time In)>0.3125, (Time Out-Time In)-((0.3125)-(Lunch/24)), 0); where 0.3125 is 7.5 (our normal workday)/24 hours in one day
The solution should be 0 (8hrs 15m worked witht 45 m lunch = 7.5 work day) but I get an output of 1:12 (h:mm format)

I'm going in circles here it seems and can't figure it out. Thank you for your time and help!

**DonkeyOte** · 04-29-2010, 01:08 PM

It might make sense to post an example that reflects your latest setup - ie lunch column etc... your last file does not have that info. so hard to comment.

Generally speaking using your last file:

Please Login or Register  to view this content.

applied to other columns.

Given roundings and precision involved when dealing with time it's generally a good idea (IMO) to use either Time Strings and coerce (as above) or use TIME function, eg:

Please Login or Register  to view this content.

or do the decimal division in the calculation

Please Login or Register  to view this content.

generally more auditable - ie when come back you're not left with an obscure decimal

In general terms as already outline Time is Decimal - ie noon = 0.5, 6 am = 0.25, 6pm = 0.75 etc... so 7.5 is 7 1/2 days as far as XL is concerned - hence the division by 24 to convert to Hours.

Originally Posted by FMGSCG

when I type '3' it enters it as '0.03' and throws off my calculations

Check your options - sounds as though you have the "Automatically Insert a Decimal Point" active (to 2 decimals)

**FMGSCG** · 04-29-2010, 01:36 PM

Sorry - forgot to attach to my latest post. Here is the updated example spreadsheet with the 'Lunch' column entered.

Thanks for the help!

**DonkeyOte** · 04-29-2010, 01:42 PM

Using your last file - you have only Day one configured presently but in essence:

F4: =MAX(0,(MOD(E4-C4,1)-(D4/24))-"7:30")
edit - updated at 18:45 GMT - erroneous ref. to D4 rather than C4

does that work for you ?

Generally speaking though you're implying In/Out & Lunch are manual entry and if so it does not make sense to mix the input types.
Presently you're implying Time entries for In/Out and Decimal entry for Lunch.
You should have all inputs entered consistently - either all Decimal or all Time (advised) - ie enter 0:45, 0:30 for lunch - at which point you can dispense with the /24 division.

**DonkeyOte** · 04-29-2010, 01:47 PM

EDIT: too late to edit original... I just noticed you entered 0.45 for 45 mins... sorry I should have noticed that earlier.

To account for this you should in fact use:

=MAX(0,MOD(E4-C4,1)-SUBSTITUTE(TEXT(D4,"0.00"),".",":")-"7:30")

(edit - AGAIN - had to add teh TEXT to account for 0.3)

As you can tell by the edits - generally better to enter Time as Time