Working with Times

[Terry VanDuzee]

Hello
Column 1 I have a start time
Column 2 I have an end time.

If column 2 is after midnight, I still need to be able to calculate the hrs
between the start time and end time.

EX:
I work from 8pm till 4 am (20:00 - 04:00 am).
I need it to calculate 8 hrs. But I get #value because 4am is past
midnight.

How can I do this?
Thanks so much
Terry V

 

[Norman Harker]

Hi Terry!

With StartTime in A1 and EndTime in B1

Try:
=(B1-A1+(B1<A1))*24

The logic is that if the stop time is less than the start time then
the implied IF statement (B1<A1) will add 1 (TRUE) (which is 24 hours)
to the calculation.

If you want the time in Time format remove the *24 and format hh:mm
but note that if you add or multiply the time is actually being stored
as a decimal part of one day.

For this and other gems on working with time sheets see:

Chip Pearson
http://www.cpearson.com/excel/overtime.htm

Bookmark Chip's topic page (link at bottom) because there's hours of
fun there.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Arvi Laanemets]

Hi

=EndTime-StartTime +(StartTime>EndTime)

 

[Terry VanDuzee]

Norman
Thank you for your reply

Im confused with the (B1<A1). I understand it says End time is less that
the start time, but Im not sure how excel is interpreting this in the
formula.

Can you give me insight into the logic of it?

Thanx a bunch
Terry V

 

[Norman Harker]

Hi Terry!

Take:

=(B1-A1+(B1<A1))*24

In a long form I could use:

=(B1-A1+IF(B1<A1,1,0))*24

If I use:

=B1<A1
It returns TRUE if B1<A1 and FALSE otherwise.

If you have Boolean TRUE in a calculation, Excel coerces it to 1.
False is Coerced to 0

So:
=(B1<A1)+0
Returns 1 if B1<A1 and 0 otherwise.

We can get a bit hung up about efficiency of formulas that are quite
short at the expense of making them easier to understand for
beginners. But if there's a probability of them being copied down many
cells, efficiency becomes important. Even for beginners / novices it
is essential that they get to grips with these concepts at an early
stage because as sure as eggs is eggs they'll hit problems later if
they don't.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Terry VanDuzee]

Norman
Thank you, that was the explanation I needed

Terry V

 

[Norman Harker]

Hi Terry!

Thanks for thanks. Never hesitate to ask for an explanation of how /
why it works.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[M@x]

Hi,

Alternative , a short way:

=MOD(B1-A1,1)

I hope translation of Formula from german is Ok (=REST(B1-A1;1)

M@x