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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
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,685
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,625
Messages
5,832,748
Members
430,163
Latest member
YesImAk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top