converting Time to units

H

hellZg8

our company works with iso standards.we need to put in our start time and
finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
at 18:00, but this is when the next shift starts 18:00 to 06:00.

A1=Start Time B1= Finished Time C1 = Actual Time
23:00 01:24 2.4

iso standards 1 unit = 6 minutes

i found one thread that show this formulaa which works up to midnight but
after that it don't

=Round((A1-B1)*24,2)

any help on this matter or any direction is greatly appreciated and Thank
You in advance.
 
H

hellZg8

Thank You for a quick response. Unfortunately this did not work

A1= Start Time 18:00
B1= Finished Time 18:30
c1=ROUND((A1+IF(B1>A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5
 
H

hellZg8

Thanks Fred, you were correct on the operator and this did work.

this formula also works
=24*(IF(A1>B1,B1+1-A1,B1-A1))

i remeber read some where once before about iso dates and times on chip
pearsons web site so I thought I'd check it out.

Thanks Chip, Fred and Neil for all Your Help

http://www.cpearson.com/excel/datearith.htm
 
F

Fred Smith

No problem. I knew you'd catch the error as well -- I just happened on the
thread before you did.
 
D

daddylonglegs

hellZg8 said:
our company works with iso standards.we need to put in our start time
and
finished time ( in 24 hour format ). The shifts start at 06:00 and
finishes
at 18:00, but this is when the next shift starts 18:00 to 06:00.

A1=Start Time B1= Finished Time C1 = Actual Time
23:00 01:24 2.4

iso standards 1 unit = 6 minutes

i found one thread that show this formulaa which works up to midnight
but
after that it don't

=Round((A1-B1)*24,2)

any help on this matter or any direction is greatly appreciated and
Thank
You in advance.

Do you need the result rounded to the nearest 6 minutes? If so you need
to round to 1 decimal place not 2. You can use this formula

=ROUND(MOD(B1-A1,1)*24,1)

alternatively, if all your start and end times are always shown in 6
minute increments then the rounding is superfluous, just use

=MOD(B1-A1,1)*24
 

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