Subtract timevalue from clocktime accross midnight

jameand

New Member
Joined
Dec 1, 2005
Messages
6
Hi, I need to subtract a TIME(a1, b1, c1) value from a clock time in "D1" and have the function work backwards over midnight. I have searched excel formula books, searched this board, and looked on-line with no clear examples. Is there a "function" I can use, or will a macro be required?
thanks in advance!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Think about this ... you want to subtract a time and have it work backwards over midnight. That means negative time! What are you expecting as an answer ... give examples. If you are really expecting negative time, what are you going to use it for?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
hello jameand and welcome to the board.

You could use this formula

=MOD(D1-MOD(TIME(A1,B1,C1),1)+1,1)
 

jameand

New Member
Joined
Dec 1, 2005
Messages
6
GlenUK,
I'm not looking for negative time. I'm looking to have a shell spreadsheet that is reuseable for setting timelines for flying. I want to enter a desired time of arrival and then have a column of cells above that fixed time that can be used to automatically calculate a clock time that occurs before the fixed(user entered) time. I think this has to include the date and time aspects, but I can't find a clear way to do it. For example, I enter an arrival time of 03:00 GMT and the takeoff time is the function of a user entere enroute time of say, 5 hours. If I use the B1=C2-TIME(a1, a2, a3) where A1= 5hours. This will result in an error instead of displaying 22:00 hours the day before the user entered time. This is what I'm trying to figure out. Any direction?
thanks in advance.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Using dates will work

Hi jameand ,

your solution depends on how you want to enter the arrival time. If you want to just type in a time ( no date ), then use the formula that barry houdini gave you.

If you have the arrival time typed in like this
12/12 03:00 AM
in, say, cell C2, and have 05:00 typed in cell A1, then a simple formula of:
Code:
=C2-A1
will give you what you want.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,265
Messages
5,577,094
Members
412,766
Latest member
BigRusty82
Top