Results 1 to 10 of 10

20040310, 12:52 #1
 Join Date
 Apr 2003
 Location
 Medina, Ohio, USA
 Posts
 44
 Thanks
 0
 Thanked 0 Times in 0 Posts
Help with Calculating Time (Excel XP)
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:004: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.

20040310, 13:14 #2
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Help with Calculating Time (Excel XP)
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
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20040310, 13:22 #3
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Help with Calculating Time (Excel XP)
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
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20040310, 13:29 #4
 Join Date
 Apr 2003
 Location
 Medina, Ohio, USA
 Posts
 44
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Calculating Time (Excel XP)
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.

20040310, 13:30 #5
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Help with Calculating Time (Excel XP)
The attached version updates Sam's answer to correct the problem with total hours being displayed incorrectly.

20040310, 13:39 #6
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Help with Calculating Time (Excel XP)
Sorry, I'm still not awake. Use the mod(time,1) function. In the F10 case:
<pre>=MOD(((C10<B10)+C10B10+(E10<D10)+E10D10),1)*24</pre>
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20040310, 13:47 #7
 Join Date
 Apr 2003
 Location
 Medina, Ohio, USA
 Posts
 44
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Calculating Time (Excel XP)
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?

20040310, 14:11 #8
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Help with Calculating Time (Excel XP)
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)

20040310, 14:52 #9
 Join Date
 Apr 2003
 Location
 Medina, Ohio, USA
 Posts
 44
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with Calculating Time (Excel XP)
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.

20040310, 16:52 #10
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Help with Calculating Time (Excel XP)
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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
<pre>=IF(COUNT(B6:E6)>1,MOD(((C6<B6)+C6B6+(E6<D6)+E6D6),1)*24,"")</pre>
2) Use the subtotal function, so that Excel won't have the dumb smart tags.:
First one:
<pre>=IF(COUNT(F7:F12)>0,SUBTOTAL(9,F6:F12),"")</pre>
Second one:
<pre>=IF(COUNT(F15:F20)>0,SUBTOTAL(9,F14:F20),"" )</pre>
Grand one:
<pre>=IF(COUNT(F6:F20)>0,SUBTOTAL(9,F6:F20),"")</pre>
I made these changes and reattached the workbook. Not sure about the righthand columns, so test them for me.
Sam<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>