To calculate the time difference you are better of with the =MOD function. The formula would be =MOD([end_time]-[start_time];1). The added benefit of this is that it also works for when the period goes past midnight, so when the start time is 23:00 and end time for example 15:30 it would calculate 16:30 so 16 hours and 30 minutes. Subtracting end-start the straight forward way would end up in an error because you'd get negative time.
When rounding to the nearest interval (15 minute or hour), mround or floor/ceiling is significantly better and less overall steps. As you can just do =floor(value,"00:15") and this will always round down.
Check out my full courses and ebooks here:
👉 www.howtoexcel.org/courses/
To calculate the time difference you are better of with the =MOD function. The formula would be =MOD([end_time]-[start_time];1). The added benefit of this is that it also works for when the period goes past midnight, so when the start time is 23:00 and end time for example 15:30 it would calculate 16:30 so 16 hours and 30 minutes. Subtracting end-start the straight forward way would end up in an error because you'd get negative time.
Thanks for sharing 14:58 👍
My first attempt with Python 😊
When rounding to the nearest interval (15 minute or hour), mround or floor/ceiling is significantly better and less overall steps.
As you can just do =floor(value,"00:15") and this will always round down.
Definitely a good tip! Thanks!
👍👍❤❤