Using an IF statement on time based data

D

DonB

I have a worksheet of payroll time data by date that i want to identify
overtime amounts in excess of 8 hours. I can't seem to write an IF statement
that correctly answers the question "If 8 hours or more, enter 8 hours, if
less then the the calculated time. It seems to be a formatting problem of
moving from time formats to number formats. HELP.
Thanks
 
D

Dave O

Seems like you should be able to do this with an IF function, assuming
you have cells that contain a start time and an end time. For example
if the start time is in A2 and the end time is in B2, you could use
this formula
=IF(B2-A2>=8/24,8,B2-A2)
.... where A2 and B2 are Excel time entries.
 
D

DonB

I guess that I am not explaining the problem very well. There are six
columns of data representing the beginning and ending times for the morning,
afternoon, and evening time frames. I have no problem in calculating the
total time worked. However, when I apply an IF statement against the total
to determine if overtime has been worked, the result is .46875 every time.
My equation is
=if(((d12-c12)+(f12-e12)+(h12-g12))>=8,8,(d12-c12)+(f12-e12)+(h12-g12)). I
have tried different formattings without success.
 
D

DonB

I guess I didn't explain my problem very well. I have six colums of data
which have the daily beginning and ending times for the morning, afternoon,
and evening shifts of an employee. I want an IF statement that says if the
total time is 8 or more hours, the result is 8, else the total time spent.
The various permutations that I have tried return the result .46875 or an
error message. The problem seems to mixing time and number formats but I
can't be sure. Thanks for the reply.
 
D

Dave O

Excel treats dates as integer numbers, and times as fractional parts of
a day. For instance, my system time is 12/2/2005 4:04:25 PM Eastern
U.S. Excel's equivalent of this timestamp is 38688.6697337963. Try
this yourself by entering =NOW(), then change that cell's format to a
number with a few decimal places.

So your .46875 may be correct if you multiply it be 24 to convert the
fractional portion of a day into hours: I get 11.25, or 11 hours 15
minutes.

How are your start times and end times entered?
 
D

Dave Peterson

Or just compare the sum with 8 hours:

=if(((d12-c12)+(f12-e12)+(h12-g12))>=time(8,0,0),time(8,0,0),
(d12-c12)+(f12-e12)+(h12-g12))

or equivalently:

I guess that I am not explaining the problem very well. There are six
columns of data representing the beginning and ending times for the morning,
afternoon, and evening time frames. I have no problem in calculating the
total time worked. However, when I apply an IF statement against the total
to determine if overtime has been worked, the result is .46875 every time.
My equation is
=if(((d12-c12)+(f12-e12)+(h12-g12))>=8,8,(d12-c12)+(f12-e12)+(h12-g12)). I
have tried different formattings without success.
Click to expand...
 
D

Dave Peterson

or equivalently...

=if(((d12-c12)+(f12-e12)+(h12-g12))>=8/24,8/24,(d12-c12)+(f12-e12)+(h12-g12))

But

=min(time(8,0,0),((d12-c12)+(f12-e12)+(h12-g12)))
or
=min(8/24,((d12-c12)+(f12-e12)+(h12-g12)))

would seem reasonable.

I guess that I am not explaining the problem very well. There are six
columns of data representing the beginning and ending times for the morning,
afternoon, and evening time frames. I have no problem in calculating the
total time worked. However, when I apply an IF statement against the total
to determine if overtime has been worked, the result is .46875 every time.
My equation is
=if(((d12-c12)+(f12-e12)+(h12-g12))>=8,8,(d12-c12)+(f12-e12)+(h12-g12)). I
have tried different formattings without success.
Click to expand...
 
D

Dave O

DonB-
Now that I think on it a little longer, your original formula appears
to have a logic flaw in it. Your formula says
=if(((d12-c12)+(f12-e12)+(h12-g12))>=8,8
.... which would award someone with 8 hours' work if they worked
anything less than 8.
 
D

DonB

I think that portion of the formula answers the question as to whether
someone has worked at least 8 hours and responds with 8 if that is true,
otherwise it will return the total hours actually worked which would be less
that 8.

I still think my problem has something to do with using the number 8 tho I
did try 8:00 which didn't help matters any.

Thanks for the help tho.
 
D

Dave Peterson

Times and dates can be confusing--especially when you're typing...

Sometimes people mean 8 when they write 8 hours. Other times 8 hours means 8/24
(or 8:00).

You may want to read Chip Pearson's notes on date/times.
http://cpearson.com/excel/datetime.htm

There is a lot of other nice stuff on that site, too.
My appologies. I went back and tried the suggestion and it does work.
Click to expand...
 
Top