Using TIME(), TIMEVALUE(), and IF() functions

  • Thread starter Thread starter Thomas Burns
  • Start date Start date
T

Thomas Burns

I want to calculate an elapsed time... easy to do, just
take the difference between two cells with datetime values.
Problem: If the ending time is after 1530, then I want the
calculation to assume an ending time 1530.
An IF() is needed. Date/times are entered with this format:
7/3/03 14:30

I'm having trouble calculating, I keep getting #VALUE
errors.
I've tried to use TIMEVALUE() without luck.
Any suggestions?
Thanks.
 
Hi Thomas
This works for me
=IF(end>TIMEVALUE("15:30"),TIMEVALUE("15:30")-start,end-start)
where "end" and "start" are cell references. Remember to format cell to
display time

Best wishes
bernardLiengme
 
Hi Bernard,

just a heads up, if the OP is using dates as well it
will always be true, nevertheless if the OP is using
times w/o dates one could use this as well

=MIN("15:30",A2)-A1

where A2 is end and A1 start

If the OP is using dates he might want to explain how the
time should be counted?
 
Very good point, Peo.
bernard

Peo Sjoblom said:
Hi Bernard,

just a heads up, if the OP is using dates as well it
will always be true, nevertheless if the OP is using
times w/o dates one could use this as well

=MIN("15:30",A2)-A1

where A2 is end and A1 start

If the OP is using dates he might want to explain how the
time should be counted?

--

Regards,

Peo Sjoblom
Click to expand...
 
Back
Top