Calculate hours - times go into next day

[Al]

Hi Guys,

I have a spreadsheet here that has start / end hours in one column ie

A B

1 17:00 2
2 19:00
3
4

In column B is the total number of hours between A1 and A2, ie A2 -
A1. This works fine with the forumla:

=(A2-INT(A2))*24-(A1-INT(A1))*24

But when the times cross midnight (and they will only ever cross
midnight up to 3am) then it doesnt work and you end up with -19 etc.

Whats the simple fix please?

Thanks

-Al

 

[Ron Rosenfeld]

Hi Guys,

I have a spreadsheet here that has start / end hours in one column ie

A B

1 17:00 2
2 19:00
3
4

In column B is the total number of hours between A1 and A2, ie A2 -
A1. This works fine with the forumla:

=(A2-INT(A2))*24-(A1-INT(A1))*24

But when the times cross midnight (and they will only ever cross
midnight up to 3am) then it doesnt work and you end up with -19 etc.

Whats the simple fix please?

Thanks

-Al

=A2-A1+(A1>A2) and format as h:mm

or, if you want decimal hours, as your formula above seems to indicate:

=(A2-A1+(A1>A2))*24

--ron

 

[Al]

=A2-A1+(A1>A2)  and format as h:mm

or, if you want decimal hours, as your formula above seems to indicate:

=(A2-A1+(A1>A2))*24

--ron

Hmm, that didnt seem to work? I got some weird values.

Yes it needs to be decimal because further into the spreadsheet it is
multiplied by a dollar value.

Cheers

-Al

 

[T. Valko]

Here's another one...

=MOD(A2-A1,1)*24

Format as General or Number

 

[Ron Rosenfeld]

Hmm, that didnt seem to work? I got some weird values.

That's not particularly informative in terms of trying to help you
troubleshoot. It works fine here, with the earlier hour in A1 and the later
hour in A2.

Yes it needs to be decimal because further into the spreadsheet it is
multiplied by a dollar value.

Then, as I wrote, use the formula I recommended with the *24

--ron

 

[Al]

Here's another one...

=MOD(A2-A1,1)*24

Format as General or Number

Hi Biff,

I like you answer, but still slightly puzzled.

If I have Column A with the two times

20:00
01:00

And then in B1 = MOD(A2-A1,1) I get 5:00 which is the correct answer.

However I cant workout why this works?

MOD should give the remainder of 1-20 when divided by 1. This is -19/1
which is 19. Why am I getting 5?

Also I need a decimal value, so I assume I should encase the whole lot
in a INT() statement?

Finally why do you *24?

TIA

-Al

 

[Al]

=A2-A1+(A1>A2)  and format as h:mm

or, if you want decimal hours, as your formula above seems to indicate:

=(A2-A1+(A1>A2))*24

--ron

Hi Ron,

Thanks for the reply.

If A1=20:00 and A2=01:00 using =(A2-A1+(A1>A2))*24 in B1 I get 0:00 ?

Where am I going wrong?

Cheers

-Al

 

[T. Valko]

= MOD(A2-A1,1)

However I cant workout why this works?

I don't know why it works, either! It's just one of the quirks that you get
used to with Excel. You would think that it shouldn't work because
subtracting the times may be a negative result. But, I know that it *does*
work and it's accurate so we just have to accept it.

Also I need a decimal value, so I assume I
should encase the whole lot in a INT() statement?
Finally why do you *24?

If you want the result to display as a time value (for example: 5:00) then
use this version and format the cell as h:mm...

=MOD(A2-A1,1)

If you want the result to be a decimal value (for example: 5) then use this
version and format the cell as either General or Number...

=MOD(A2-A1,1)*24


--
Biff
Microsoft Excel MVP

Here's another one...

=MOD(A2-A1,1)*24

Format as General or Number

Hi Biff,

I like you answer, but still slightly puzzled.

If I have Column A with the two times

20:00
01:00

And then in B1 = MOD(A2-A1,1) I get 5:00 which is the correct answer.

However I cant workout why this works?

MOD should give the remainder of 1-20 when divided by 1. This is -19/1
which is 19. Why am I getting 5?

Also I need a decimal value, so I assume I should encase the whole lot
in a INT() statement?

Finally why do you *24?

TIA

-Al

 

[Steve Dunn]

Hi, sorry to butt in, but it should be pointed out that this isn't an Excel
quirk, it is the correct result of a modulus operation, which returns the
"non-negative common residue" of two numbers (not quite the same as the
remainder).

Modulus operations can be thought of as circular, with the clock being the
most common example: Mod(1-2,12) would give the answer 11 which is what you
would expect when you look 2 hours back from 1 o'clock.

Now look at Mod(-5,2): imagine drawing a "clock" with only two "hours" on
it. i.e. a mark at the top (representing 0/2), and a mark at the bottom
(representing 1). Go back 5 "hours" from 0, and you would read the answer
as 1, not -1.

The same applies here, but what you must remember is that time is not stored
or calculated in hours, it is stored/calculated in days. i.e. 20:00 is not
the same as the number 20, it is actually 20/24ths of a day, or 0.8333...
days.

Now look at your problem like this: 01:00 - 20:00 or
0.041667 - 0.8333 = -0.791667, which, when when put into Mod(-0.791667,1),
results in 0.208333 (5/24ths of a day), which ,when displayed in time
format, is shown as 05:00, or, when multiplied by 24 is 5.

I hope this makes more sense.

Steve D.

 

[Roger Govier]

Very nice explanation, Steve!

 

[Steve Dunn]

Thanks Roger, that's appreciated.

Very nice explanation, Steve!

 

[T. Valko]

Yeah, great explanation!

I'm going to copy that and add it to my notes.

 

[Steve Dunn]

Please do. I'm just glad it made sense, I very nearly scrapped it.

Cheers
Steve D.