Subtract timevalue from clocktime accross midnight

[jameand]

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!

 

[GlennUK]

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]

hello jameand and welcome to the board.

You could use this formula

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

 

[jameand]

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]

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:

=C2-A1

will give you what you want.