Why is E3=>4 FALSE?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Excel tells me that E3=>4 is FALSE, where E3=4:45, a time format, and
formatting should be ignored.
Click to expand...

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")
 
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
 
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))
 
Thanks, Joeu2004, but I'm not sure how to enter your argument in my whole
formula. How would you do it?
 
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
 
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?
 
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?

David Biddulph said:
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

johnthebaptist said:
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?
Click to expand...
Click to expand...
 
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
 
2:43 is *not* >= 4:00 so you don't satisfy the condition for "DONE".
--
David Biddulph

johnthebaptist said:
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?

David Biddulph said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

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

johnthebaptist said:
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?

David Biddulph said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate hours, past midnight etc 4
Wrong count of time 7
Value of minutes not calculated 8
Extracting just the time portion 5
Formula 6
Replacing COUNTIFS with COUNTIF 3
Date formula resulting in Year, Months & days 3
Day formula 4

Back
Top