CALCULATE A SHIFT IN HOURS PAST MIDNIGHT

[Eddie G.]

how do you calculate a shift length in hours for ending times past midnight:

17:00 - 02:00 (the next morning answer should be nine hours

 

[Anne Troy]

Eddie: That's not a problem at all. You have to understand that whether you enter the values or not...

a) anytime you enter a date in Excel, it is also entering a time value.

b) anytime you enter a time in Excel, it is also entering a date value.

Because Excel, unless you tell it otherwise, gives *today's* date, you're probably entering a start time of 17:00 and an end time of 02:00, but you're not typing in the date, so it thinks it's the same date. If you're subtracting, Excel will NOT allow negative time values, so you're probably getting ###################, right?

Try fixing it by putting the correct dates into the cells too.

It's a little easier than it sounds. For instance, you can enter "5 p" to enter 5:00 pm today, or "4/8 5 p" to enter 5:00 pm yesterday. You would have to type "4/8 5:30 p" to get that time yesterday. It doesn't matter how your cells are formatted to be viewed, you can still enter your time/dates this way.

 

[Legacy 98055]

Hi
Example:

In Cell A1 04/01/02 17:00
In Cell B1 04/02/02 02:00

Formula in Cell C1:
=B1-A1

Format Cell C1 for HH:MM

To do this:
1. Right click on cell C1
2. Choose Format Cells
3. Choose the number tab
4. Choose Time
5. Choose hh:mm or example may = 13:30
6. Click 'Ok'

Do a search on this site if you need more help. There are multiple examples of dealing with dates and times...

Have a Nice Day!

Tom

 

[Dave Patton]

=B1+(B1<A1)-A1

The B1<A1 adds 1 if True

The above gives time is 9:00

For a decimal result

=(B1+(B1<A1)-A1)*24

Result is 9.00
This message was edited by Dave Patton on 2002-04-09 10:24