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

[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.

 

[Bernard V Liengme]

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

 

[Peo Sjoblom]

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?

 

[Bernard V Liengme]

Very good point, Peo.
bernard

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