How do I calc the time difference in hh:mm for shift work?

[TomS]

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.

 

[Niek Otten]

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.

 

[Fred Smith]

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.

 

[Sum Ting Wong]

=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.

 

[Fred Smith]

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.

 

[Archimedes' Lever]

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.

 

[David Biddulph]

Answers interspersed below.
--
David Biddulph

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.

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.

 

[Archimedes' Lever]

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.

As far as display of the figure goes, sure. But the formula will not
work without the use of the number 24 simply because that is how large
the set is. That forces the input to a set of 24. Everywhere I used it
requires me to use 24 hr format for data input (maybe it is my
validation) as using 11:00 for example and 1:00 as a stop time does not
yield 2 hr. I have to use 13:00 for that to work right.

So what am I missing? Unless the user types out, long form and adds
the AM or PM designator, the data entry fails to calculate correctly in
the tally cell.

 

[David Biddulph]

If you want to use 12 hour format, then of course you need to include the AM
or PM. That's what 12 hour format is.

As I said in the part of my message which you snipped, data validation and
cell display formatting are two separate unrelated features of Excel. Data
validation can constrain the numbers that it will allow you to store in the
cell. The format in which you choose to display the number is a separate
matter.

 

[Archimedes' Lever]

If you want to use 12 hour format, then of course you need to include the AM
or PM. That's what 12 hour format is.

People entering start times or stop times typically do not want to futz
around with any more than the least sized set of keypresses. So most
would opt for hh:mm entry, which means that one MUST use 24 hr format for
a sheet set up for quick entry.

So you will see time tracking where 24 hr format is utilized more often
than forcing a person to enter long format time declarations.

 

[Archimedes' Lever]

If you want to use 12 hour format, then of course you need to include the AM
or PM. That's what 12 hour format is.

As I said in the part of my message which you snipped, data validation and
cell display formatting are two separate unrelated features of Excel. Data
validation can constrain the numbers that it will allow you to store in the
cell. The format in which you choose to display the number is a separate
matter.

The only exception, then, is if one chooses "custom" as the cell
format, and sets that string to "hh:mm". Maybe it is a bug. ;-)

That is why my cells would not accept any other input. After choosing
one of the given "time" formats for cells, it works fine. Apparently my
"custom" setup also forces a specific input mode, which ends up being a
type of validation.

 

[Fred Smith]

You're not giving up, are you?

I agree people typically want to enter data with the minimum of keystrokes.
I always enter times using 24-hour notation, regardless of how the cell is
formatted. However, formatting a cell and entering data are two separate and
distinct functions. You can easily enter a 24-hour time into a cell that's
formatted for AM/PM.

You could even do it yourself before you insist that it can't be done in
your next reply.

Regards,
Fred.

 

[David Biddulph]

Yes, if it behaves that way for you, it's a bug. Perhaps you can tell us
which version of Excel you are using?

For the rest of the world, we can custom format as hh:mm and still type in a
number as 0.75 or 6:00 PM. It will still display as 18:00, but doesn't
constrain the format in which we enter the number, nor does that format
contrain the value which we can enter.