Help with Calculating Time (Excel XP)

Topic

New Reply

I think we’re making this harder than it is and the answer is right in front of our nose but for some reason we just can’t get it. Forgive me if this is too basic for some of you.

I have attached a timesheet. We’ve already searched on this site and many others and have found good examples but none do (at least we think) what we need or maybe it’s just the simple fact that we don’t know how to tweak them to do so. So here are my questions/problems:

1. We need to calculate Total hours worked per day and week and have them displayed as a decimal…..such as 8.0 not 8:00. We can create a custom format to show the Total hours worked per week as h.mm and we’ve done so however when we do that the Week total calculates incorrectly. (See cell F13). So, can we display time as a decimal but still have the other formulas calculate using the actual time increment? If so how do we do it?

2. We record time in 1/4 hr increments, so if I work from 7:00-4:45 and take an hour lunch, I should have 8.75 hrs. not 8.45 hrs. as shown in cell F6. Suggestions on how to make that work?

3. If we enter 12:00 AM in a cell, such as C15 it won’t display. Ideas as to why?

We would appreciate any help or suggestions on this.

Reply | Quote

Replies

Time is kept as a fractional part of a day, so if you want decimal hours, you need to multiply by 24 and format as a number. I made these changes in the attached. HTH –Sam

Reply | Quote

Sammy

Thanks for the post. We’ve done that before in our formula experimenting but we now have just created two problems:

1. Cell F10 shows 33 hours. This becomes a problem only when a user doesn’t take a lunch. So, how do we adjust the formula to compensate for that? I don’t know if we can do both and are incorrectly mixing apples and oranges here.

2. I also just noticed that the week total is still wrong calcuating 65.25 hrs. How do we fix that?

Again thanks for any help here.

Reply | Quote

Sorry, I’m still not awake. Use the mod(time,1) function. In the F10 case:

=MOD(((C10<B10)+C10-B10+(E10<D10)+E10-D10),1)*24

Reply | Quote

Tony and Sammy

THANK YOU SO much………..this is EXACTLY what we were missing. We tried several variations of IF and other functions but never would have thought of the MOD function. I apologize if this sounds, well “stupid” but why does that work and what exactly is it doing?

Reply | Quote

The original spreadsheet displayed the total hours worked in h:mm format. In rows 9 & 10 there are no values in columns C & D. This causes the result in column F to be 24 hours too high if the start and end times are on the same day. The h:mm format will only display values up to 24 hours, effectively ignoring complete days. So in cell F10 though the answer is 33 hours it is displayed as 9 hours. The result is still stored as 1 day 9 hours, and it is this value that other calculations will use.

As times are stored by Excel as decimals with 24 hours = 1, the MOD function is used to remove the integer part of the result leaving only the proportion of 1 day (9 hours in this case)

Reply | Quote

Tony

This completely makes sense now and we were so close at one point with one of our formulas, I can’t believe it (obviously not the one posted in the attachment though). I can’t tell you how many different variations of formulas we have across tons of test spreadsheets – it was getting more confusing keeping it all straight. Now we can scrap them all. I think it’s interesting that out of everything we found on the Internet none of them had this function in there’s either……so I guess that’s why their timesheets did the same thing as ours.

We can’t thank you enough……really.

Reply | Quote

Sorry not to respond, I was away, but Tony did a great job with the explanation. Two other things that you may want to change:
1) Use an if function with a count function to get rid of the zeros when the entries are blank.:
Now the formula really looks ugly!

=IF(COUNT(B6:E6)>1,MOD(((C6<B6)+C6-B6+(E6<D6)+E6-D6),1)*24,"")

2) Use the subtotal function, so that Excel won’t have the dumb smart tags.:
First one:

=IF(COUNT(F7:F12)>0,SUBTOTAL(9,F6:F12),"")

Second one:

=IF(COUNT(F15:F20)>0,SUBTOTAL(9,F14:F20),"")

Grand one:

=IF(COUNT(F6:F20)>0,SUBTOTAL(9,F6:F20),"")

I made these changes and reattached the workbook. Not sure about the right-hand columns, so test them for me.
–Sam

Reply | Quote

Sorry not to respond, I was away, but Tony did a great job with the explanation. Two other things that you may want to change:
1) Use an if function with a count function to get rid of the zeros when the entries are blank.:
Now the formula really looks ugly!

=IF(COUNT(B6:E6)>1,MOD(((C6<B6)+C6-B6+(E6<D6)+E6-D6),1)*24,"")

2) Use the subtotal function, so that Excel won’t have the dumb smart tags.:
First one:

=IF(COUNT(F7:F12)>0,SUBTOTAL(9,F6:F12),"")

Second one:

=IF(COUNT(F15:F20)>0,SUBTOTAL(9,F14:F20),"")

Grand one:

=IF(COUNT(F6:F20)>0,SUBTOTAL(9,F6:F20),"")

I made these changes and reattached the workbook. Not sure about the right-hand columns, so test them for me.
–Sam

Reply | Quote

The original spreadsheet displayed the total hours worked in h:mm format. In rows 9 & 10 there are no values in columns C & D. This causes the result in column F to be 24 hours too high if the start and end times are on the same day. The h:mm format will only display values up to 24 hours, effectively ignoring complete days. So in cell F10 though the answer is 33 hours it is displayed as 9 hours. The result is still stored as 1 day 9 hours, and it is this value that other calculations will use.

As times are stored by Excel as decimals with 24 hours = 1, the MOD function is used to remove the integer part of the result leaving only the proportion of 1 day (9 hours in this case)

Reply | Quote

Tony and Sammy

THANK YOU SO much………..this is EXACTLY what we were missing. We tried several variations of IF and other functions but never would have thought of the MOD function. I apologize if this sounds, well “stupid” but why does that work and what exactly is it doing?

Reply | Quote

Sorry, I’m still not awake. Use the mod(time,1) function. In the F10 case:

=MOD(((C10<B10)+C10-B10+(E10<D10)+E10-D10),1)*24

Reply | Quote

Sammy

Thanks for the post. We’ve done that before in our formula experimenting but we now have just created two problems:

1. Cell F10 shows 33 hours. This becomes a problem only when a user doesn’t take a lunch. So, how do we adjust the formula to compensate for that? I don’t know if we can do both and are incorrectly mixing apples and oranges here.

2. I also just noticed that the week total is still wrong calcuating 65.25 hrs. How do we fix that?

Again thanks for any help here.

Reply | Quote

The attached version updates Sam’s answer to correct the problem with total hours being displayed incorrectly.

Reply | Quote

The attached version updates Sam’s answer to correct the problem with total hours being displayed incorrectly.

Reply | Quote

Time is kept as a fractional part of a day, so if you want decimal hours, you need to multiply by 24 and format as a number. I made these changes in the attached. HTH –Sam

Reply | Quote

Sorry, I forgot about item three. If you use the Tools>Options>View tab and check “Zero values” at the bottom, you will see 12:00 AM. –Sam

Reply | Quote

Sorry, I forgot about item three. If you use the Tools>Options>View tab and check “Zero values” at the bottom, you will see 12:00 AM. –Sam

Reply | Quote