Calculate hours - times go into next day

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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
Click to expand...

=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
 
=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
Click to expand...

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
 
Hmm, that didnt seem to work? I got some weird values.
Click to expand...

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.
Click to expand...

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

--ron
 
Here's another one...

=MOD(A2-A1,1)*24

Format as General or Number
Click to expand...

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
 
=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
Click to expand...

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
 
= MOD(A2-A1,1)
However I cant workout why this works?
Click to expand...

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?
Click to expand...

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
Click to expand...

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
 
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.
 
Back
Top