Subtracting time / from before midnight to the next day

Evil Twin

New Member
Joined
Jan 27, 2012
Messages
37
Hi Everyone,
I am sure this has been posted before but I cant see anything obvious that will solve my problem.

I am trying to work out the response times to an event that my staff attend ( such as a fire alarm)

in C1 will be the time the event was called in, 23:55
In E1 will be the arrival time, 23:59
In F1 I want to show the time taken to arrive on site (response time)

This seems straightforward until the arrival time goes into the next day, such as 00:05 and this is when I have the problem

To make it more complicated, sometimes there is no need for an arrival time to be entered for some events ,with the end user leaving it blank or putting "NA" or "na" etc into the cell
I have tried the below formula which works to a point but leave me with an ######### error if the time is after midnight

=IF(E1="NA",0,IF(E1="n/a",0,E1-C1))

Any assistance will be great,


All the best,
Evil Twin

Insanity is a gift....
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try this in cell F1:

=IF(NOT(ISNUMBER(E1)),"",IF(E1<c1,(e1+24)-c1,e1-c1))
LESS THAN SYMBOL HERE

Note: The board keeps chopping my formula at the less than symbol. Here's the part of the formula which should appear AFTER the less than symbol:

C1,(E1+24)-C1,E1-C1))

Is this happening to anyone else?<c1,(e1+24)-c1,e1-c1))[ code]<="" html=""></c1,(e1+24)-c1,e1-c1))[></c1,(e1+24)-c1,e1-c1))
 
Last edited:
Upvote 0
Try this in cell F1:

=IF(NOT(ISNUMBER(E1)),"",IF(E1<c1,(e1+24)-c1,e1-c1))
LESS THAN SYMBOL HERE

Note: The board keeps chopping my formula at the less than symbol. Here's the part of the formula which should appear AFTER the less than symbol:

C1,(E1+24)-C1,E1-C1))

Is this happening to anyone else?<c1,(e1+24)-c1,e1-c1))[ code]<="" html=""></c1,(e1+24)-c1,e1-c1))[>



Hi Rob,

Pure genius, works a treat...

Where do you guys learn all this stuff?


All the best,
Steve,

Insanity is a gift...</c1,(e1+24)-c1,e1-c1))
 
Upvote 0
I don't think you want to use 24 in the formula - the result might look right but you will actually have 23 days more in the result than you should have (format result cell as general to see what I mean) which can mess up any further calculations - try with a 1, i.e.

=IF(E1="NA",0,IF(E1="n/a",0,IF(E1< C1,1+E1-C1,E1-C1)))

[xladept's version will also work]

Note that the board sometimes interprets > and < symbols as HTML tags, try leaving a space at the open end as per my formula to make sure it displays correctly


 
Upvote 0
Note: The board keeps chopping my formula at the less than symbol. Here's the part of the formula which should appear AFTER the less than symbol:
The board sees them as HTML tags either leave a space after and before them i.e. =IF(NOT(ISNUMBER(E1)),"",IF(< C1,(E1+24)-C1,E1-C1) >1) and advise the OP to remove the spaces or put the whole formula in PHP tags i.e.
PHP:
=IF(NOT(ISNUMBER(E1)),"",IF(<C1,(E1+24)-C1,E1-C1))

Edit: Barry got in before me but I might as well leave this up.
 
Last edited:
Upvote 0
Hi Evil Twin,

Try:

Code:
=IF(E1="N/A",0,MOD(E1-C1,1))

Hi XLAdept,

Your solution also works fine if "N/A" is showing , and with a quick adjustment to allow for other entry styles such as "na" it does the trick perfectly,

Top MAN....


All the best,
Steve


Insanity is a gift....
 
Upvote 0
Hi Barry,

What can I say but thanks for the extra care for my problem, much appreciated.

All the best,
Evil Twin,

Insanity is a gift....
 
Upvote 0
Hi Evil Twin,

For me it was a toss up between "You can't do one thing" and "Help stamp out mental health"(!)
 
Upvote 0

Forum statistics

Threads
1,212,095
Messages
6,105,933
Members
447,985
Latest member
gigi11

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
Back
Top