Subtracting time / from before midnight to the next day

[Evil Twin]

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

 

[rob.barnes01]

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

 

[xladept]

Hi Evil Twin,

Try:

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

 

[Evil Twin]

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

 

[barry houdini]

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

 

[rob.barnes01]

Thanks, Barry!

 

[MARK858]

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.

=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.

 

[Evil Twin]

Hi Evil Twin,

Try:

=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....

 

[Evil Twin]

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

 

[xladept]

Hi Evil Twin,

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