# Subtracting time / from before midnight to the next day

#### Evil Twin

##### New Member
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

### 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:
Hi Evil Twin,

Try:

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

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,

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)))

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

Thanks, Barry!

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:
Hi Evil Twin,

Try:

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

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

Hi Barry,

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

All the best,
Evil Twin,

Hi Evil Twin,

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

Replies
2
Views
241
Replies
0
Views
2K
Replies
23
Views
2K
Replies
1
Views
270
Replies
3
Views
1K

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.

### Which adblocker are you using?

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

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