converting Time to units
 

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.

converting Time to units
 

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message ...
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.

converting Time to units
 

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(B1A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message ...
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.

converting Time to units
 

The operator should have been '<', not '':

=round((A1+if(b1<a1,1,0)-b1)*24,2)

--
Regards,
Fred


"hellZg8" wrote in message
...
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(B1A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message
...
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.

converting Time to units
 

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

this formula also works
=24*(IF(A1B1,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

"Fred Smith" wrote:

The operator should have been '<', not '':

=round((A1+if(b1<a1,1,0)-b1)*24,2)

--
Regards,
Fred


"hellZg8" wrote in message
...
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(B1A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message
...
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.

converting Time to units
 

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

--
Regards,
Fred


"hellZg8" wrote in message
...
Thanks Fred, you were correct on the operator and this did work.

this formula also works
=24*(IF(A1B1,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

"Fred Smith" wrote:

The operator should have been '<', not '':

=round((A1+if(b1<a1,1,0)-b1)*24,2)

--
Regards,
Fred


"hellZg8" wrote in message
...
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(B1A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message
...
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.

converting Time to units
 

hellZg8 Wrote:
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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522576

converting Time to units
 

EXTREMELY HELPFUL for my application as well! THANKS

"Fred Smith" wrote:

The operator should have been '<', not '':

=round((A1+if(b1<a1,1,0)-b1)*24,2)

--
Regards,
Fred


"hellZg8" wrote in message
...
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(B1A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message
...
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.

converting Time to units
 

EXTREMELY HELPFUL for my application as well! THANKS

"Niek Otten" wrote:

=ROUND((A1+IF(B1A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

"hellZg8" wrote in message ...
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.