Why is E3=>4 FALSE?

[johnthebaptist]

Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.

The whole formula I'm trying to work out is: =IF(E3=>4,"DONE",(4-D3)).
Excel accepts this but reads 4 as 24 and returns 19:15 for (4-D3).

OK, so (E3=>4) is FALSE if 4=24, but why does Excel read 4 as 24? And how
do I get Excel to read 4 as 4:00 and return the difference between 4:00 and,
say, 3:27?

 

[Gord Dibben]

4:45 as a number is 0.197916667 if AM

0.697916667 if PM

For more on Excel's use of date/time serial numbers see Chip Pearson's site.

http://www.cpearson.com/excel/datetime.htm#SerialDates


Gord Dibben MS Excel MVP

 

[joeu2004]

Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.

You will get some insight into the problem if you reformat the cell as
Number. Time is stored as fractions of a day. 4:45 is stored as
4.75/24. One solution:

E3>=timevalue("4:00")

 

[Bernard Liengme]

Time is stores as a fraction of a day. So 4:45 is 0.1979 (4.75 hours / 24
hrs/day = 0.1979 days)
You could use E3*24>4 or E3>4/24 or E3>Time(4,0,0)
best wishes

 

[David Biddulph]

First point, the synax for "greater than or equals" is >=, not =>

Secondly, Excel date & time format counts in units of one day (24 hours), so
your time 4:45 in E3 hjas been compared with 4 days (96 hours).

You are saying that Excel reads 4 as 24, but you have in fact subtracted
4:45 from 4 days (96 hours), and got a result of 91:15, which is what you'd
have seen if the cell was formatted as [h]:mm, but as you'd formatted the
cell as something like h:mm you have not displayed the first 3 days, but
just the remaining 19:15.

If you want to compare with (and subtract from) 4 hours, not 4 days, then
you could try something like
=IF(E3>=(--("4:00")),"DONE",("4:00"-D3)) or
=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))

 

[johnthebaptist]

Thanks, Gord Dibben, but I don't see how this info solves my problem.

 

[johnthebaptist]

Thanks, Joeu2004, but I'm not sure how to enter your argument in my whole
formula. How would you do it?

 

[Peo Sjoblom]

Did you actually read the info? It might make you understand that 4:45 is
not greater than 4 since 1 hour = 1/24 thus 4 hours and 45 minutes are 4/24
+ 45/24/60 meaning that even 0.5 is greater than 4:45. To convert 4:45 to a
decimal value you need to multply with 24

=E3*24=>4


--


Regards,


Peo Sjoblom

 

[johnthebaptist]

Looks good, Bernard Liengme, but I'm not sure how to enter one of your
variations of the argument in my whole formula. Will you help me with this?

 

[johnthebaptist]

Much obliged, David Biddulph. I entered
<=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))> and it returned <3:47> with
E3 as <2:43>. I expected <DONE>. What happened?

First point, the synax for "greater than or equals" is >=, not =>

Secondly, Excel date & time format counts in units of one day (24 hours), so
your time 4:45 in E3 hjas been compared with 4 days (96 hours).

You are saying that Excel reads 4 as 24, but you have in fact subtracted
4:45 from 4 days (96 hours), and got a result of 91:15, which is what you'd
have seen if the cell was formatted as [h]:mm, but as you'd formatted the
cell as something like h:mm you have not displayed the first 3 days, but
just the remaining 19:15.

If you want to compare with (and subtract from) 4 hours, not 4 days, then
you could try something like
=IF(E3>=(--("4:00")),"DONE",("4:00"-D3)) or
=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))
--
David Biddulph

Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.

The whole formula I'm trying to work out is: =IF(E3=>4,"DONE",(4-D3)).
Excel accepts this but reads 4 as 24 and returns 19:15 for (4-D3).

OK, so (E3=>4) is FALSE if 4=24, but why does Excel read 4 as 24? And how
do I get Excel to read 4 as 4:00 and return the difference between 4:00
and,
say, 3:27?

 

[Gord Dibben]

Just trying to point out that 4:45 is not greater than 4

It could solve your problem if you grasped that concept.

Chip's site explains how Excel's date serials can be used to calculate.


Gord

 

[David Biddulph]

2:43 is *not* >= 4:00 so you don't satisfy the condition for "DONE".
--
David Biddulph

Much obliged, David Biddulph. I entered
<=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))> and it returned <3:47>
with
E3 as <2:43>. I expected <DONE>. What happened?

First point, the synax for "greater than or equals" is >=, not =>

Secondly, Excel date & time format counts in units of one day (24 hours),
so
your time 4:45 in E3 hjas been compared with 4 days (96 hours).

You are saying that Excel reads 4 as 24, but you have in fact subtracted
4:45 from 4 days (96 hours), and got a result of 91:15, which is what
you'd
have seen if the cell was formatted as [h]:mm, but as you'd formatted the
cell as something like h:mm you have not displayed the first 3 days, but
just the remaining 19:15.

If you want to compare with (and subtract from) 4 hours, not 4 days, then
you could try something like
=IF(E3>=(--("4:00")),"DONE",("4:00"-D3)) or
=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))
--
David Biddulph

message

Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.

The whole formula I'm trying to work out is: =IF(E3=>4,"DONE",(4-D3)).
Excel accepts this but reads 4 as 24 and returns 19:15 for (4-D3).

OK, so (E3=>4) is FALSE if 4=24, but why does Excel read 4 as 24? And
how
do I get Excel to read 4 as 4:00 and return the difference between 4:00
and,
say, 3:27?

 

[johnthebaptist]

A logician I am not, nor a mathematician, but <3:47> as the difference
between TIME(4,0,0) and <2:43> does look kind of funny. I just added the two
<>s, got <6:20>, and concluded, "I'm DONE with my homily prep."

Solution: correct my formula. *D3,* <0:13>, a subtotal, should be *E3,*
<2:43>, total to the present. That done, I see I still have <1:17> prep time
to go.

I love this. Believe me, my homilies make a little more sense than my first
formula and the conclusion I drew from it.

2:43 is *not* >= 4:00 so you don't satisfy the condition for "DONE".
--
David Biddulph

Much obliged, David Biddulph. I entered
<=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))> and it returned <3:47>
with
E3 as <2:43>. I expected <DONE>. What happened?

First point, the synax for "greater than or equals" is >=, not =>

Secondly, Excel date & time format counts in units of one day (24 hours),
so
your time 4:45 in E3 hjas been compared with 4 days (96 hours).

You are saying that Excel reads 4 as 24, but you have in fact subtracted
4:45 from 4 days (96 hours), and got a result of 91:15, which is what
you'd
have seen if the cell was formatted as [h]:mm, but as you'd formatted the
cell as something like h:mm you have not displayed the first 3 days, but
just the remaining 19:15.

If you want to compare with (and subtract from) 4 hours, not 4 days, then
you could try something like
=IF(E3>=(--("4:00")),"DONE",("4:00"-D3)) or
=IF(E3>=TIME(4,0,0),"DONE",(TIME(4,0,0)-D3))
--
David Biddulph

message
Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.

The whole formula I'm trying to work out is: =IF(E3=>4,"DONE",(4-D3)).
Excel accepts this but reads 4 as 24 and returns 19:15 for (4-D3).

OK, so (E3=>4) is FALSE if 4=24, but why does Excel read 4 as 24? And
how
do I get Excel to read 4 as 4:00 and return the difference between 4:00
and,
say, 3:27?