Help with formula 09:30-10:00

asked 2018-02-07 07:30:48 +0200

redd62 gravatar image

Departure should be 10:00 however it left earlier by 30mins. How is it formulated please?

edit retag flag offensive close merge delete

Comments

The question is vague. There's no need to use terms "Departure", "left earlier" etc. Instead, please say what you have and want to see in what cells, like:

"I have 10:00 in A1, and 9:30 in B1. I want to see -0:30 in C1. How to do that?"

I am not sure that my interpretation of your question is correct, so please edit your question to make it more specific.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-07 07:34:49 +0200 )edit

Great! That is exactly what i want to see. Thanks

redd62 gravatar imageredd62 ( 2018-02-07 07:49:25 +0200 )edit

Then use [HH]:MM format string for C1 with formula =B1-A1.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-07 07:55:07 +0200 )edit

Also other way around....8:00 in A1 and 8:45 in B1...i want to see 0:45 in c1

redd62 gravatar imageredd62 ( 2018-02-07 07:55:12 +0200 )edit

Got it. What if it goes next day? A1 23:00 B1 02:00 C1 03:00

redd62 gravatar imageredd62 ( 2018-02-07 08:01:07 +0200 )edit

Use dates in A1 and B1.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-07 08:05:04 +0200 )edit

I just tried 26:00 instead of 02:00 - looks good. It gave me 03:00.....thanks mucho!

redd62 gravatar imageredd62 ( 2018-02-07 08:11:51 +0200 )edit

sigh... why don't people want to do it right way, and invent workarounds like "26:00" instead of "2018-02-07 02:00" (optionally formatted as "HH:MM" to only show the time), so that later one could expand or make statistics, and could get sensible result from correct data...

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-07 08:20:12 +0200 )edit