Example of my data

Col A1 = 16:00 (4PM) in time format
Col B1 = 24:00 (midnight)
Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct.

However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I get
a
#VALUE! error.

B should be larger than A, to avoid negative times, which Excel doesn't
represent very well.
One option is to use 0:00 instead of 24:00
Or include dates in the times.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"TomS" wrote in message
...

Example of my data

Col A1 = 16:00 (4PM) in time format
Col B1 = 24:00 (midnight)
Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct.

However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I
get
a
#VALUE! error.

Why do you convert your result to text?

Use:
=mod(b1-a1,1) and format the result as h:mm

If you have to have the result as text, use:
=text(mod(b1-a1,1),"h:mm")

Regards,
Fred.

"TomS" wrote in message
...

Example of my data

Col A1 = 16:00 (4PM) in time format
Col B1 = 24:00 (midnight)
Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct.

However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I
get
a
#VALUE! error.

=IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24))

G10 is start time, H10 is stop time. Change to your start and stop
cell names.

Sorry, but 24 hour format is required here. There may be a 12 hr
format version I do not know about.

Format of a cell is immaterial to its calculation. Formatting controls only
the display, not the way it's stored internally. Any formula that works with
time, will work with all formats of it.

In your response, you should indicate that you are converting time to
decimal hours. The result would need to be formatted as a number.


Regards,
Fred.
"Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org
wrote in message ...




=IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24))

G10 is start time, H10 is stop time. Change to your start and stop
cell names.

Sorry, but 24 hour format is required here. There may be a 12 hr
format version I do not know about.

On Tue, 28 Apr 2009 08:03:27 -0600, "Fred Smith"
wrote:

Format of a cell is immaterial to its calculation.

Perhaps, but the code given is 24 hr explicit code, if you'll simply
look at the numbers used to perform the calculations.

Formatting controls only
the display, not the way it's stored internally.

It also becomes a data validator as it only allows data to be entered
into such a cell in a matching format.

Any formula that works with
time, will work with all formats of it.

OK

In your response, you should indicate that you are converting time to
decimal hours. The result would need to be formatted as a number.

Yes, the answer is a decimal version, but anyone that tracks time
segments in other than quarter hours is nuts to begin with, so I would
also limit input into the time fields to quarter hour break points.




Regards,
Fred.
"Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org
wrote in message ...




=IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24))

G10 is start time, H10 is stop time. Change to your start and stop
cell names.

Sorry, but 24 hour format is required here. There may be a 12 hr
format version I do not know about.

Answers interspersed below.
--
David Biddulph

"Archimedes' Lever" wrote in message
...
On Tue, 28 Apr 2009 08:03:27 -0600, "Fred Smith"
wrote:

Format of a cell is immaterial to its calculation.

Perhaps, but the code given is 24 hr explicit code, if you'll simply
look at the numbers used to perform the calculations.

Whether the time is displayed in 12 hour or 24 hour format will make no
difference to the result of the calculation, as Fred said.

Formatting controls only
the display, not the way it's stored internally.

It also becomes a data validator as it only allows data to be entered
into such a cell in a matching format.

Data validation and cell display formatting are two distinct features in
Excel. You can't use cell display formatting to perform the data validation
function. A cell displayed in 24 hour format doesn't prevent anyone
entering time in 12 hour format, nor does it prevent time beyond 24 hours
being entered.

Any formula that works with
time, will work with all formats of it.

OK

In your response, you should indicate that you are converting time to
decimal hours. The result would need to be formatted as a number.

Yes, the answer is a decimal version, but anyone that tracks time
segments in other than quarter hours is nuts to begin with, so I would
also limit input into the time fields to quarter hour break points.

Regards,
Fred.
"Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org
wrote in message ...

=IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24))

G10 is start time, H10 is stop time. Change to your start and stop
cell names.

Sorry, but 24 hour format is required here. There may be a 12 hr
format version I do not know about.