Times
 

I have a drop down list in C5 and D5 with times ranging from 12.00 AM to
11.45 PM.

I have the following formula in E5 which works well to calculate hours
worked:-
=IF(OR(C5="",D5=""),"",IF(D5<C5,D5+1,D5)-C5)

What I would like to do is incorporate the following:-
=IF(OR(C5="",D5=""),"",IF(AND(C5=5.45 pm,D5=18.30
pm),1,IF(D5<C5,D5+1,D5)-C5))
(Obviously the above syntax is incorrect)

In other words if the time in C5 is 5.45pm AND the time in D5 is 6.30pm then
the total in E5 would be 1 hour.

C5 and D5 are formatted as h.mm AM/PM and E5 as h.mm

Any help appreciated.
Sandy

Times
 

This way:
=IF(OR(C5="",D5=""),"",IF(AND(C5=TIME(17,45,0),D5= (18,30,0)),TIME(1,0,0),IF(D5<C5,D5+1,D5)-C5))

Regards,
Fred



"Sandy" wrote in message
...
I have a drop down list in C5 and D5 with times ranging from 12.00 AM to
11.45 PM.

I have the following formula in E5 which works well to calculate hours
worked:-
=IF(OR(C5="",D5=""),"",IF(D5<C5,D5+1,D5)-C5)

What I would like to do is incorporate the following:-
=IF(OR(C5="",D5=""),"",IF(AND(C5=5.45 pm,D5=18.30
pm),1,IF(D5<C5,D5+1,D5)-C5))
(Obviously the above syntax is incorrect)

In other words if the time in C5 is 5.45pm AND the time in D5 is 6.30pm
then the total in E5 would be 1 hour.

C5 and D5 are formatted as h.mm AM/PM and E5 as h.mm

Any help appreciated.
Sandy

Times
 

Sandy,
Times (and dates and dates w/time) are stored internally as decimal values
and are just displayed as m/d/yy h:m:s as you have formatted the cell(s). So
rather than checking for C=5.45 pm,D5=18.30 pm you need to be testing for the
decimal values for those times:
5:45 p.m. is 0.739583333 and
6:30 p.m. is 0.770833333
so your tests would become
C5=.739583333,D5=.770833333
or even
C5=(17.75/24),D5=(18.5/24)


"Sandy" wrote:

I have a drop down list in C5 and D5 with times ranging from 12.00 AM to
11.45 PM.

I have the following formula in E5 which works well to calculate hours
worked:-
=IF(OR(C5="",D5=""),"",IF(D5<C5,D5+1,D5)-C5)

What I would like to do is incorporate the following:-
=IF(OR(C5="",D5=""),"",IF(AND(C5=5.45 pm,D5=18.30
pm),1,IF(D5<C5,D5+1,D5)-C5))
(Obviously the above syntax is incorrect)

In other words if the time in C5 is 5.45pm AND the time in D5 is 6.30pm then
the total in E5 would be 1 hour.

C5 and D5 are formatted as h.mm AM/PM and E5 as h.mm

Any help appreciated.
Sandy