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.

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

--
Kind regards,

Niek Otten

"hellZg8" <hellZg8@discussions.microsoft.com> wrote in message news:8423EA76-36FA-4782-A2AB-D4C58162DABB@microsoft.com...
> 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.
>
>

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

"Niek Otten" wrote:

> =ROUND((A1+IF(B1>A1,1,0)-B1)*24,2)
>
> --
> Kind regards,
>
> Niek Otten
>
> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message news:8423EA76-36FA-4782-A2AB-D4C58162DABB@microsoft.com...
> > 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.
> >
> >
>
>
>

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

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

--
Regards,
Fred


"hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
news:C9C9250C-4C48-4BC5-B7DA-BEB84330837C@microsoft.com...
> 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
>
> "Niek Otten" wrote:
>
>> =ROUND((A1+IF(B1>A1,1,0)-B1)*24,2)
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
>> news:8423EA76-36FA-4782-A2AB-D4C58162DABB@microsoft.com...
>> > 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.
>> >
>> >
>>
>>
>>

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

"Fred Smith" wrote:

> The operator should have been '<', not '>':
>
> =round((A1+if(b1<a1,1,0)-b1)*24,2)
>
> --
> Regards,
> Fred
>
>
> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
> news:C9C9250C-4C48-4BC5-B7DA-BEB84330837C@microsoft.com...
> > 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
> >
> > "Niek Otten" wrote:
> >
> >> =ROUND((A1+IF(B1>A1,1,0)-B1)*24,2)
> >>
> >> --
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
> >> news:8423EA76-36FA-4782-A2AB-D4C58162DABB@microsoft.com...
> >> > 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.
> >> >
> >> >
> >>
> >>
> >>
>
>
>

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

--
Regards,
Fred


"hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
news:F5FC5B1E-A645-410C-8E71-BA69B3FD4F61@microsoft.com...
> 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
>
> "Fred Smith" wrote:
>
>> The operator should have been '<', not '>':
>>
>> =round((A1+if(b1<a1,1,0)-b1)*24,2)
>>
>> --
>> Regards,
>> Fred
>>
>>
>> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
>> news:C9C9250C-4C48-4BC5-B7DA-BEB84330837C@microsoft.com...
>> > 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
>> >
>> > "Niek Otten" wrote:
>> >
>> >> =ROUND((A1+IF(B1>A1,1,0)-B1)*24,2)
>> >>
>> >> --
>> >> Kind regards,
>> >>
>> >> Niek Otten
>> >>
>> >> "hellZg8" <hellZg8@discussions.microsoft.com> wrote in message
>> >> news:8423EA76-36FA-4782-A2AB-D4C58162DABB@microsoft.com...
>> >> > 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.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>