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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula than understands midnight? 26
Averaging times that cross midnight? 4
TIME CALCULATION 8
Excel - Convert int value secs past midnight into time 53100=2:45 1
IF statement with time over midnight 3
Hundredths of Seconds Since Midnight 1
If statement based on time ranges 2
time clock wrap around midnight 3

Top