software

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(B1gt;A1,1,0)-B1)*24,2)

--
Kind regards,

Niek Otten

quot;hellZg8quot; gt; wrote in message ...
gt; our company works with iso standards.we need to put in our start time and
gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt;
gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; 23:00 01:24 2.4
gt;
gt; iso standards 1 unit = 6 minutes
gt;
gt; i found one thread that show this formulaa which works up to midnight but
gt; after that it don't
gt;
gt; =Round((A1-B1)*24,2)
gt;
gt; any help on this matter or any direction is greatly appreciated and Thank
gt; You in advance.
gt;
gt;
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(B1gt;A1,1,0)-B1)*24,2)
result in C1 was 23.5 where it should be 0.5

quot;Niek Ottenquot; wrote:

gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;hellZg8quot; gt; wrote in message ...
gt; gt; our company works with iso standards.we need to put in our start time and
gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt; gt;
gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; gt; 23:00 01:24 2.4
gt; gt;
gt; gt; iso standards 1 unit = 6 minutes
gt; gt;
gt; gt; i found one thread that show this formulaa which works up to midnight but
gt; gt; after that it don't
gt; gt;
gt; gt; =Round((A1-B1)*24,2)
gt; gt;
gt; gt; any help on this matter or any direction is greatly appreciated and Thank
gt; gt; You in advance.
gt; gt;
gt; gt;
gt;
gt;
gt;

The operator should have been 'lt;', not 'gt;':

=round((A1 if(b1lt;a1,1,0)-b1)*24,2)

--
Regards,
Fredquot;hellZg8quot; gt; wrote in message
...
gt; Thank You for a quick response. Unfortunately this did not work
gt;
gt; A1= Start Time 18:00
gt; B1= Finished Time 18:30
gt; c1=ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt; result in C1 was 23.5 where it should be 0.5
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt;gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt;gt;
gt;gt; --
gt;gt; Kind regards,
gt;gt;
gt;gt; Niek Otten
gt;gt;
gt;gt; quot;hellZg8quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; our company works with iso standards.we need to put in our start time and
gt;gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt;gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt;gt; gt;
gt;gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt;gt; gt; 23:00 01:24 2.4
gt;gt; gt;
gt;gt; gt; iso standards 1 unit = 6 minutes
gt;gt; gt;
gt;gt; gt; i found one thread that show this formulaa which works up to midnight but
gt;gt; gt; after that it don't
gt;gt; gt;
gt;gt; gt; =Round((A1-B1)*24,2)
gt;gt; gt;
gt;gt; gt; any help on this matter or any direction is greatly appreciated and Thank
gt;gt; gt; You in advance.
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Thanks Fred, you were correct on the operator and this did work.

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

www.cpearson.com/excel/datearith.htm

quot;Fred Smithquot; wrote:

gt; The operator should have been 'lt;', not 'gt;':
gt;
gt; =round((A1 if(b1lt;a1,1,0)-b1)*24,2)
gt;
gt; --
gt; Regards,
gt; Fred
gt;
gt;
gt; quot;hellZg8quot; gt; wrote in message
gt; ...
gt; gt; Thank You for a quick response. Unfortunately this did not work
gt; gt;
gt; gt; A1= Start Time 18:00
gt; gt; B1= Finished Time 18:30
gt; gt; c1=ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt; gt; result in C1 was 23.5 where it should be 0.5
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt;gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Kind regards,
gt; gt;gt;
gt; gt;gt; Niek Otten
gt; gt;gt;
gt; gt;gt; quot;hellZg8quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; our company works with iso standards.we need to put in our start time and
gt; gt;gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt; gt;gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt; gt;gt; gt;
gt; gt;gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; gt;gt; gt; 23:00 01:24 2.4
gt; gt;gt; gt;
gt; gt;gt; gt; iso standards 1 unit = 6 minutes
gt; gt;gt; gt;
gt; gt;gt; gt; i found one thread that show this formulaa which works up to midnight but
gt; gt;gt; gt; after that it don't
gt; gt;gt; gt;
gt; gt;gt; gt; =Round((A1-B1)*24,2)
gt; gt;gt; gt;
gt; gt;gt; gt; any help on this matter or any direction is greatly appreciated and Thank
gt; gt;gt; gt; You in advance.
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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

--
Regards,
Fredquot;hellZg8quot; gt; wrote in message
...
gt; Thanks Fred, you were correct on the operator and this did work.
gt;
gt; this formula also works
gt; =24*(IF(A1gt;B1,B1 1-A1,B1-A1))
gt;
gt; i remeber read some where once before about iso dates and times on chip
gt; pearsons web site so I thought I'd check it out.
gt;
gt; Thanks Chip, Fred and Neil for all Your Help
gt;
gt; www.cpearson.com/excel/datearith.htm
gt;
gt; quot;Fred Smithquot; wrote:
gt;
gt;gt; The operator should have been 'lt;', not 'gt;':
gt;gt;
gt;gt; =round((A1 if(b1lt;a1,1,0)-b1)*24,2)
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt; Fred
gt;gt;
gt;gt;
gt;gt; quot;hellZg8quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thank You for a quick response. Unfortunately this did not work
gt;gt; gt;
gt;gt; gt; A1= Start Time 18:00
gt;gt; gt; B1= Finished Time 18:30
gt;gt; gt; c1=ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt;gt; gt; result in C1 was 23.5 where it should be 0.5
gt;gt; gt;
gt;gt; gt; quot;Niek Ottenquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Kind regards,
gt;gt; gt;gt;
gt;gt; gt;gt; Niek Otten
gt;gt; gt;gt;
gt;gt; gt;gt; quot;hellZg8quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; our company works with iso standards.we need to put in our start time
gt;gt; gt;gt; gt; and
gt;gt; gt;gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and
gt;gt; gt;gt; gt; finishes
gt;gt; gt;gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt;gt; gt;gt; gt; 23:00 01:24 2.4
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; iso standards 1 unit = 6 minutes
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; i found one thread that show this formulaa which works up to midnight
gt;gt; gt;gt; gt; but
gt;gt; gt;gt; gt; after that it don't
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =Round((A1-B1)*24,2)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; any help on this matter or any direction is greatly appreciated and
gt;gt; gt;gt; gt; Thank
gt;gt; gt;gt; gt; You in advance.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;

hellZg8 Wrote:
gt; our company works with iso standards.we need to put in our start time
gt; and
gt; finished time ( in 24 hour format ). The shifts start at 06:00 and
gt; finishes
gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt;
gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; 23:00 01:24 2.4
gt;
gt; iso standards 1 unit = 6 minutes
gt;
gt; i found one thread that show this formulaa which works up to midnight
gt; but
gt; after that it don't
gt;
gt; =Round((A1-B1)*24,2)
gt;
gt; any help on this matter or any direction is greatly appreciated and
gt; Thank
gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=522576EXTREMELY HELPFUL for my application as well! THANKS

quot;Fred Smithquot; wrote:

gt; The operator should have been 'lt;', not 'gt;':
gt;
gt; =round((A1 if(b1lt;a1,1,0)-b1)*24,2)
gt;
gt; --
gt; Regards,
gt; Fred
gt;
gt;
gt; quot;hellZg8quot; gt; wrote in message
gt; ...
gt; gt; Thank You for a quick response. Unfortunately this did not work
gt; gt;
gt; gt; A1= Start Time 18:00
gt; gt; B1= Finished Time 18:30
gt; gt; c1=ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt; gt; result in C1 was 23.5 where it should be 0.5
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt;gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Kind regards,
gt; gt;gt;
gt; gt;gt; Niek Otten
gt; gt;gt;
gt; gt;gt; quot;hellZg8quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; our company works with iso standards.we need to put in our start time and
gt; gt;gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt; gt;gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt; gt;gt; gt;
gt; gt;gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; gt;gt; gt; 23:00 01:24 2.4
gt; gt;gt; gt;
gt; gt;gt; gt; iso standards 1 unit = 6 minutes
gt; gt;gt; gt;
gt; gt;gt; gt; i found one thread that show this formulaa which works up to midnight but
gt; gt;gt; gt; after that it don't
gt; gt;gt; gt;
gt; gt;gt; gt; =Round((A1-B1)*24,2)
gt; gt;gt; gt;
gt; gt;gt; gt; any help on this matter or any direction is greatly appreciated and Thank
gt; gt;gt; gt; You in advance.
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

EXTREMELY HELPFUL for my application as well! THANKS

quot;Niek Ottenquot; wrote:

gt; =ROUND((A1 IF(B1gt;A1,1,0)-B1)*24,2)
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;hellZg8quot; gt; wrote in message ...
gt; gt; our company works with iso standards.we need to put in our start time and
gt; gt; finished time ( in 24 hour format ). The shifts start at 06:00 and finishes
gt; gt; at 18:00, but this is when the next shift starts 18:00 to 06:00.
gt; gt;
gt; gt; A1=Start Time B1= Finished Time C1 = Actual Time
gt; gt; 23:00 01:24 2.4
gt; gt;
gt; gt; iso standards 1 unit = 6 minutes
gt; gt;
gt; gt; i found one thread that show this formulaa which works up to midnight but
gt; gt; after that it don't
gt; gt;
gt; gt; =Round((A1-B1)*24,2)
gt; gt;
gt; gt; any help on this matter or any direction is greatly appreciated and Thank
gt; gt; You in advance.
gt; gt;
gt; gt;
gt;
gt;
gt;