I am trying to find the difference between 2 times to determine the amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20 instead
of 4?

Format cells as General


"erighter" wrote:

I am trying to find the difference between 2 times to determine the amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20 instead
of 4?

Formatting cells as general does not help. For example, the difference
between 9:30 AM and 4:30 AM is appearing as a negative number now.

9:30 AM - 4:30 AM = -2.79


"Teethless mama" wrote:

Format cells as General

Try formatting the cell entries as h:mm AM/PM (found in custom) and for the
difference calculation h:mm (also in custom) and just use =A2-A1 for the
formula

"erighter" wrote:

I am trying to find the difference between 2 times to determine the amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20 instead
of 4?

Tried formatting you suggested, still issue.
for example:

A1 = 4:30 AM
A2 = 9:30 AM

What's the difference in time between these?
(A2 will always be greater or later in the day than A1)

A2 - A1 = 19:00 - - - I am expecting a result of 5 hours though, not 19.

"Shane" wrote:

Try formatting the cell entries as h:mm AM/PM (found in custom) and for the
difference calculation h:mm (also in custom) and just use =A2-A1 for the
formula

"erighter" wrote:

I am trying to find the difference between 2 times to determine the amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20 instead
of 4?

On 23 Jul, 21:30, erighter wrote:
Tried formatting you suggested, still issue.
for example:

A1 = 4:30 AM
A2 = 9:30 AM

What's the difference in time between these?
(A2 will always be greater or later in the day than A1)

A2 - A1 = 19:00 - - - I am expecting a result of 5 hours though, not 19.

"Shane" wrote:
Try formatting the cell entries as h:mm AM/PM (found in custom) and for the
difference calculation h:mm (also in custom) and just use =A2-A1 for the
formula

"erighter" wrote:

I am trying to find the difference between 2 times to determine the amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20 instead
of 4?

strange when i typed the above in it worked. check to make sure the
cell is time. i.e. When you look at cell format you see h:mm AM/PM

matthew

The only way that I can get 19:00 from 4:30 in A1, 9:30 in A2 & 24 is:

=(A1-A2)+24

This *shows* as 19:00 but is actually 23 days 19 hours
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"erighter" wrote in message
...
Tried formatting you suggested, still issue.
for example:

A1 = 4:30 AM
A2 = 9:30 AM

What's the difference in time between these?
(A2 will always be greater or later in the day than A1)

A2 - A1 = 19:00 - - - I am expecting a result of 5 hours though, not 19.

"Shane" wrote:

Try formatting the cell entries as h:mm AM/PM (found in custom) and for
the
difference calculation h:mm (also in custom) and just use =A2-A1 for the
formula

"erighter" wrote:

I am trying to find the difference between 2 times to determine the
amount of
time lost between 2 events.

A1: First event ends at 6:00 AM (time format)
A2: Second event begins at 10:00 AM (also time format)

I'd like to express the time lost between those two in hh:mm format.
(A2 - A1)*24 = 20:00 --- I'd expect 04:00 to show that there's 4 hrs
between.

But, when A1 = 4:30 AM and A2 = 9:30 AM, the calculation correctly
shows
05:00
in time lost.

Where am I going wrong in the first example that gives result of 20
instead
of 4?