time around midnight

M

Mai-Britt

How do I convert time of day to time according to midnight? Ex. 21:37 to
-2,23? Or 02:43 to +2,43? Midnight is defined as 0.
 
T

Toppers

With time in A1 ...for 21:47 ...

=(HOUR(1-A1)+MINUTE(1-A1)*0.01)*-1

For 02:43 ...

=HOUR(B1)+MINUTE(B1)*0.01

With I assume some test (IF condition) if time is before or after MIDDAY?
 
B

bj

If I am reading what you want correctly, tr
=if(mod(time_entry,1)<0.5,hour(time_entry)+minute(time_entry)/100,-hour(1-mod(time_entry,1))-minute(1-mod(time_entry,1))/100
 
Top