Calculate how many hours between two times are before midnight and after midnight

[annazet]

Hi everyone,

How would you return number of hours between hour x and midnight and number of hours between midnight and hour y?
A1: start time (hour x)
B1: end time (hour y)
C1: sum of hours between A1 and midnight
D1: sum of hours between midnight and B1

Is there a light way of doing it please?
Came up with this but not sure if its right:

Please Login or Register  to view this content.

Could you please suggest a good/alternative way of approaching this?

[Roel Jongman]

Based on your own formula (for rownr) and result in true time notation

use in C4 (before midnight)

Please Login or Register  to view this content.

use in D4 (after midnight)

Please Login or Register  to view this content.

A good thing to remember is that times are always a fraction of 1. so 1 = 24hrs exactly.
hours after midnight dont need a calculation as every day starts counting from 0 so 2 o'clock is also 2 hours after midnight only posible AM/PM notations needs to be omitted to show 1PM as 13:00 after midnight

see attachment for result. play with start and end time to see the outcome change

[annazet]

Based on your own formula (for rownr) and result in true time notation

use in C4 (before midnight)

Please Login or Register  to view this content.

use in D4 (after midnight)

Please Login or Register  to view this content.

A good thing to remember is that times are always a fraction of 1. so 1 = 24hrs exactly.
hours after midnight dont need a calculation as every day starts counting from 0 so 2 o'clock is also 2 hours after midnight only posible AM/PM notations needs to be omitted to show 1PM as 13:00 after midnight

see attachment for result. play with start and end time to see the outcome change

Thank you; always overcomplicate things; thank you for taking this burden off my shoulders xx

[Roel Jongman]

Your welcome, I also used to struggle with time calculations for a long time, but at some point the penny dropped on how they work in excel and now it is simple not to overcomplicate.