Thanks:  0
Likes:  0

# Thread: CALCULATE A SHIFT IN HOURS PAST MIDNIGHT

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

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

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

4. =B1+(B1
The B1
The above gives time is 9:00

For a decimal result

=(B1+(B1
Result is 9.00

[ This Message was edited by: Dave Patton on 2002-04-09 10:24 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•