Time Calculation Q

[John]

I have the following formula that is not producing the correct result for me

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

It is formatted as [h]:mm

In my instance E16 is greater than 6 but less than 7.9999999, so the result
should be 0.5, which when formatted as [h]:mm should produce 0:30.

My answer returns 0

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

Where am I going wrong?

[Ragdyer]

Does this answer your question:
Key into A1,
7:05
In B1, enter this,
=A1*24
and format B1 to General or number.

Now do you see where you were going wrong?

Check out this link of Chip Pearson:

http://www.cpearson.com/excel/datetime.htm#AddingTimes
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John" <john@yahoooo.co> wrote in message
news:b3Pae.52141$Z14.42080@news.indigo.ie...
> I have the following formula that is not producing the correct result for
me
>
>
=(IF(AND(E16>=4.5,E16<=5.999999999),0.25,IF(AND(E16>=6,E16<=7.999999999999),
0.5,IF(AND(E16<4.499999999,E16>0.00001),0,IF(E16>8,0.75,0)))))
>
> It is formatted as [h]:mm
>
> In my instance E16 is greater than 6 but less than 7.9999999, so the
result
> should be 0.5, which when formatted as [h]:mm should produce 0:30.
>
> My answer returns 0
>
> E16 is also formatted as [h]:mm and returns a value of 7:05 which is = to
> 7.083333
>
> Where am I going wrong?
>
>

[Ron Rosenfeld]

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

[John]

No not really

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:unBDRWOSFHA.1096@tk2msftngp13.phx.gbl...
> Does this answer your question:
> Key into A1,
> 7:05
> In B1, enter this,
> =A1*24
> and format B1 to General or number.
>
> Now do you see where you were going wrong?
>
> Check out this link of Chip Pearson:
>
> http://www.cpearson.com/excel/datetime.htm#AddingTimes
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "John" <john@yahoooo.co> wrote in message
> news:b3Pae.52141$Z14.42080@news.indigo.ie...
>> I have the following formula that is not producing the correct result for
> me
>>
>>
> =(IF(AND(E16>=4.5,E16<=5.999999999),0.25,IF(AND(E16>=6,E16<=7.999999999999),
> 0.5,IF(AND(E16<4.499999999,E16>0.00001),0,IF(E16>8,0.75,0)))))
>>
>> It is formatted as [h]:mm
>>
>> In my instance E16 is greater than 6 but less than 7.9999999, so the
> result
>> should be 0.5, which when formatted as [h]:mm should produce 0:30.
>>
>> My answer returns 0
>>
>> E16 is also formatted as [h]:mm and returns a value of 7:05 which is = to
>> 7.083333
>>
>> Where am I going wrong?
>>
>>
>

[John]

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

[Ragdyer]

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
>
>

[Ron Rosenfeld]

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

[John]

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

[Ron Rosenfeld]

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

>Thanks Ron, good spot

You're welcome.
--ron

[selami güzel]

hi john;

please try this formula. FIRST ENTER BEGINING TIME TO CELL "C6" THEN
FINISHING TIME TO "C7" AND THEN COPY TO FOLLOWING FORMULA TO "C8" . I think you will solve your problem. You can not your problem pls.inform me.

sincerely

selami güzel

=IF((OR(C7="";C6=""));0;IF((C7<C6);((C7-C6)*24)+24;(C7-C6)*24))