Time function

S

srpavar

I have a time in A1 as 07:30 AM as start time,
B1 as 15:30 PM as end time and
in C1 column I enter the actual time like 13:00 PM, that means he goes early
by 02:30hrs early than scheduled time or I enter 17:30 PM in C1 column if he
goes
by 02:00hrs late than scheduled time.
i need a formula which give me a result in (-) or (+) in column D1 like
-02:30hrs or 02:00hrs can anyone help me to solve this problem. Thanks in
Advance for help.
 
D

David Biddulph

It's not 15:30 PM.
It's either 15:30 (without the PM), or it's 3:30 PM.

Having corrected that, you can use =C1-B1 to show the difference, but if you
want to show negative times you'll need to use Tools/ Options/ Calculation:
1904 date system, which can cause problems if you want to transfer data to
and fro between 1904 and the default 1900 date system.

If you don't want to use 1904 date system and you don't need to use the D1
result in further calculation, you can cheat and use the formula
=IF(C1-B1>=0,C1-B1,"-"&TEXT(B1-C1,"hh:mm"))
 

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

Top