TIME IN to TIME OUT = HOURS formula on Numbers 09 - PLEASE HELP!

You have to be very careful with Date&Time values when you are using them as Time only. Whether it is displayed in the cell or not, there is an implied date. It is best to strip the date off of it:

=HOUR(B)-HOUR(A)+IF(HOUR(B)<HOUR(A),24,0)

I used a slightly different approach which requires a few extras columns (to keep the formulas simpler):

The cells shaded light yellow may be hidden as they are intermediate cells:

C2=DUR2HOURS(B2-A2)+IF(DUR2HOURS(B2-A2)<0, 24, 0)

D2=INT(C2)

E2=INT(60*(C2-D2)+0.5)

F2=D2&"h "&E2&"m"

select these four cells, and fill down

I feel it is important to strip the date from Date & Time cells that are being used for time of day only. There was a discussion a while back about the timesheet template where this was a major problem. Editing of the time entry popup resulted in "times" that were months (or maybe more than a year) different than the other times in the popup and this threw all the calculations way off.

Sorry, my mistake. HOUR only returns integer hours.

=24*(TIMEVALUE(B)-TIMEVALUE(A)+IF(TIMEVALUE(B)<TIMEVALUE(A),1,0))

Keep in mind.... the method I provided does not address the problem Badunit highlights which is that the "Date" portion of the cell contents may change to many days different (if you enter a time on a different day then when you originally entered the time).

Here is an updated attempt to address this:

C2=IFERROR(TIMEVALUE(A2),"")

D2=IFERROR(TIMEVALUE(B2),"")

E2=IFERROR(IF(D2<C2, 1-(C2-D2), D2-C2),"")

F2=IFERROR(INT(24*E2),"")

G2=IFERROR(ROUND(MOD(24*60*E2, 60),0),"")

H2=IF(F2="", "", F2&"h "&G2&"m")

All the iferror() stuff is to supress entries in calculation cells when there is no time entry

Lastly... format the time entry cells as time only:

Hi D,

You may also find my comments regarding a similar question in this thread useful: how do i change hours on timesheet.

Regards,

Barry

Actually, the DUR2HOURS function is perfect there. So long as the date and time are present in the format and you actually change the date on the post-midnight time it works like a charm...

EX:

Cell A1 11/10/2011 9:00:00 AM

Cell B1 11/11/2011 2:00:00 AM

Cell C1: =DUR2HOURS (B1-A1)

Result is 17, expressed as a regular number that you can use in calculations.

Good luck!

Crystal

There are several more formulas/functions that I use for a complicated timecard (used in the IATSE union for sound editors on feature films and television shows). I would be happy to share this with you and to advise you on your specific case. Mine has complicated rules which require me to compare hours worked, elapsed hour from call time, duration of rest periods and how they affect overtime payment. As an example, if a mixer works 2 hours, is dismissed for 3 then works 5 more, I must pay them 10 hours, including the 3 hours the were dismissed. If they were dismissed for 5 hours then brought back for 5 hours of work I would pay for the 8 hours actually worked, one of which at overtime. For even though they never exceeded 8 hours, the last hour took place after 12 hours elapsed from call time. The rules become even more complicated with minimum calls, and night premiumss.

I trust that you could derive much use from my work and that it would be quite simple for me to help with your project. I can't imagine a timecard more complicated than this one!

You may email me at crystal@kingsoundworks.com