Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default subtracting time going backwards past midnight?

I am trying to track time going backwards. In other words I am backing into a
start time. I am having problems when I cross midnight going backwards.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default subtracting time going backwards past midnight?

Try this:

A1 = 7:00 PM
B1 = 3:00 AM

=MOD(B1-A1,1)

Format as h:mm = 8:00

Or, if you want the result in decimal format:

=MOD(B1-A1,1)*24

Format as GENERAL or NUMBER = 8


--
Biff
Microsoft Excel MVP


"Billman" wrote in message
...
I am trying to track time going backwards. In other words I am backing into
a
start time. I am having problems when I cross midnight going backwards.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,091
Default subtracting time going backwards past midnight?

You will have a problem there. Excel maintains times as fractions of 24
hours. To keep accurate time, you must also take date into account.
as Excel maintains time and dates as date.time That is to say, the date is
maintained as a whole number and the time as a fraction.

Tyro
"Billman" wrote in message
...
I am trying to track time going backwards. In other words I am backing into
a
start time. I am having problems when I cross midnight going backwards.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default subtracting time going backwards past midnight?

=B1-A1+(A1B1)

Fomat cell as h:mm


"Billman" wrote:

I am trying to track time going backwards. In other words I am backing into a
start time. I am having problems when I cross midnight going backwards.

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
Time after midnight Steved Excel Worksheet Functions 2 May 30th 07 08:10 PM
time around midnight Mai-Britt Excel Worksheet Functions 2 May 9th 07 01:05 PM
Calculating time past midnight. mailrail Excel Discussion (Misc queries) 3 April 18th 07 07:39 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


All times are GMT +1. The time now is 01:56 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"