CALCULATE A SHIFT IN HOURS PAST MIDNIGHT

Eddie G. · Apr 9th, 2002, 10:22 AM

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 · Apr 9th, 2002, 11:09 AM

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.

Tom Schreiner · Apr 9th, 2002, 11:12 AM

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 · Apr 9th, 2002, 11:22 AM

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

Apr 9th, 2002, 11:09 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 10:22 AM	#1
Eddie G. Board Regular Join Date: Feb 2002 Location: OKC Posts: 98	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

Apr 9th, 2002, 11:12 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 9th, 2002, 11:22 AM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	=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 ]