Time Calculation Q

On Sun, 24 Apr 2005 16:37:35 +0100, "John" <john@yahoooo.co> wrote:

>E16 is also formatted as [h]:mm and returns a value of 7:05 which is = to
>7.083333

Actually, no.

7.083333 in a cell that is formatted as [h]:mm would display as 170:00

7:05 in that cell is equal to 0.2951388888889


--ron

Think I've got the correct formula

=(IF(AND((E16*24)>=4.5,(E16*24)<=5.999999999),0.25,IF(AND((E16*24)>=6,(E16*24)<=7.999999999999),0.5,IF(AND((E16*24)<4.499999999,(E16*24)>0.00001),0,IF((E16*24)>8,0.75,0)))))/24

Its all about multiplying the source by 24

Thanks


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:jfin61d6g07ub5pnlg26p6ag14r6v6d73e@4ax.com...
> On Sun, 24 Apr 2005 16:37:35 +0100, "John" <john@yahoooo.co> wrote:
>
>>E16 is also formatted as [h]:mm and returns a value of 7:05 which is = to
>>7.083333
>
> Actually, no.
>
> 7.083333 in a cell that is formatted as [h]:mm would display as 170:00
>
> 7:05 in that cell is equal to 0.2951388888889
>
>
> --ron

And you said that you didn't get:

=A1*24
???
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John" <john@yahoooo.co> wrote in message
news:RZPae.52144$Z14.42142@news.indigo.ie...
> Think I've got the correct formula
>
>
=(IF(AND((E16*24)>=4.5,(E16*24)<=5.999999999),0.25,IF(AND((E16*24)>=6,(E16*2
4)<=7.999999999999),0.5,IF(AND((E16*24)<4.499999999,(E16*24)>0.00001),0,IF((
E16*24)>8,0.75,0)))))/24
>
> Its all about multiplying the source by 24
>
> Thanks
>
>
> "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
> news:jfin61d6g07ub5pnlg26p6ag14r6v6d73e@4ax.com...
> > On Sun, 24 Apr 2005 16:37:35 +0100, "John" <john@yahoooo.co> wrote:
> >
> >>E16 is also formatted as [h]:mm and returns a value of 7:05 which is =
to
> >>7.083333
> >
> > Actually, no.
> >
> > 7.083333 in a cell that is formatted as [h]:mm would display as 170:00
> >
> > 7:05 in that cell is equal to 0.2951388888889
> >
> >
> > --ron
>
>

On Sun, 24 Apr 2005 17:40:09 +0100, "John" <john@yahoooo.co> wrote:

>Think I've got the correct formula
>
>=(IF(AND((E16*24)>=4.5,(E16*24)<=5.999999999),0.25,IF(AND((E16*24)>=6,(E16*24)<=7.999999999999),0.5,IF(AND((E16*24)<4.499999999,(E16*24)>0.00001),0,IF((E16*24)>8,0.75,0)))))/24
>
>Its all about multiplying the source by 24
>
>Thanks
>

That's right. Time is stored as a fraction of a day.

A shorter way of writing your formula:

=(IF(E16<4.5/24,0,IF(E16<6/24,0.25,IF(E16<8/24,0.5,0.75))))/24

However, and I think it is an oversight in your formula, look at these series
of results:

Time My Formula Your Formula

7:59 0:30 0:30
8:00 0:45 0:00
8:01 0:45 0:45

Somehow, I don't think you want to see a '0' if the time is exactly 8:00

You also have left undefined any instance where the contents of E16 is
negative. In my formula it will result in a '0'.




--ron

Thanks Ron, good spot


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:v2rn61peo7j0f9uc7ljgbl3q72qk7c7jct@4ax.com...
> On Sun, 24 Apr 2005 17:40:09 +0100, "John" <john@yahoooo.co> wrote:
>
>>Think I've got the correct formula
>>
>>=(IF(AND((E16*24)>=4.5,(E16*24)<=5.999999999),0.25,IF(AND((E16*24)>=6,(E16*24)<=7.999999999999),0.5,IF(AND((E16*24)<4.499999999,(E16*24)>0.00001),0,IF((E16*24)>8,0.75,0)))))/24
>>
>>Its all about multiplying the source by 24
>>
>>Thanks
>>
>
> That's right. Time is stored as a fraction of a day.
>
> A shorter way of writing your formula:
>
> =(IF(E16<4.5/24,0,IF(E16<6/24,0.25,IF(E16<8/24,0.5,0.75))))/24
>
> However, and I think it is an oversight in your formula, look at these
> series
> of results:
>
> Time My Formula Your Formula
>
> 7:59 0:30 0:30
> 8:00 0:45 0:00
> 8:01 0:45 0:45
>
> Somehow, I don't think you want to see a '0' if the time is exactly 8:00
>
> You also have left undefined any instance where the contents of E16 is
> negative. In my formula it will result in a '0'.
>
>
>
>
> --ron

On Mon, 25 Apr 2005 15:31:38 +0100, "John" <john@yahoooo.co> wrote:

>Thanks Ron, good spot

You're welcome.
--ron