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

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

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]

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

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

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

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

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

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

Its all about multiplying the source by 24

Thanks

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]

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

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]

Thanks Ron, good spot

You're welcome.
--ron