Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Calculating time past midnight.

I'm trying to write a formula that will calculate my employees' total time
worked. A problem arises when I have people scheduled to work past midnight.
If, say, someone works from 9 pm until 3 am, how do I get Excel to recognize
the change in dates and properly figure the time?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Calculating time past midnight.

Start time in A1, end time in B1

=B1-A1+(B1<A1)

(format result as time)

or

=MOD(B1-A1,1)


will return 6:00 with 09:00 PM in A1 and 03:00 AM in B1


--
Regards,

Peo Sjoblom

"mailrail" wrote in message
...
I'm trying to write a formula that will calculate my employees' total time
worked. A problem arises when I have people scheduled to work past
midnight.
If, say, someone works from 9 pm until 3 am, how do I get Excel to
recognize
the change in dates and properly figure the time?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default Calculating time past midnight.

Assuming that your times are XL times like 21:00 and 3:00 and Start time in
B2, End time in C2 then try:

=MOD(C2-B2,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"mailrail" wrote in message
...
I'm trying to write a formula that will calculate my employees' total time
worked. A problem arises when I have people scheduled to work past
midnight.
If, say, someone works from 9 pm until 3 am, how do I get Excel to
recognize
the change in dates and properly figure the time?



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Calculating time past midnight.

You may want to include the date and time in the cell. Then you can do just
regular arithmetic to get the hours.

mailrail wrote:

I'm trying to write a formula that will calculate my employees' total time
worked. A problem arises when I have people scheduled to work past midnight.
If, say, someone works from 9 pm until 3 am, how do I get Excel to recognize
the change in dates and properly figure the time?


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to calculate time difference crossing midnight ditorejax Excel Worksheet Functions 3 August 17th 06 04:46 PM
subtraction off time after midnight Steved Excel Worksheet Functions 2 May 24th 06 01:46 AM
Calculating Time Past Midnight Darren Excel Worksheet Functions 6 March 13th 06 04:03 PM
Calculating time interval (some across midnight) [email protected] Excel Worksheet Functions 3 January 17th 06 06:50 PM
Calculating time difference over midnight! sygazelle Excel Discussion (Misc queries) 4 September 29th 05 04:59 PM


All times are GMT +1. The time now is 08:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"