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?

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

On Sun, 11 Nov 2007 10:00:00 -0800, johnthebaptist
wrote:

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?

On Nov 11, 10:00 am, johnthebaptist
wrote:
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")

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*244 or E34/24 or E3Time(4,0,0)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"johnthebaptist" wrote in 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?

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" wrote in 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?

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

"Gord Dibben" wrote:

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

On Sun, 11 Nov 2007 10:00:00 -0800, johnthebaptist
wrote:

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?

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

"joeu2004" wrote:

On Nov 11, 10:00 am, johnthebaptist
wrote:
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")

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" wrote in message
...
Thanks, Gord Dibben, but I don't see how this info solves my problem.

"Gord Dibben" wrote:

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

On Sun, 11 Nov 2007 10:00:00 -0800, johnthebaptist
wrote:

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?

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?

"Bernard Liengme" wrote:

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*244 or E34/24 or E3Time(4,0,0)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"johnthebaptist" wrote in 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?

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" wrote:

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" wrote in 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?

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

On Mon, 12 Nov 2007 13:45:02 -0800, johnthebaptist
wrote:

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

"Gord Dibben" wrote:

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

On Sun, 11 Nov 2007 10:00:00 -0800, johnthebaptist
wrote:

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?

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

"johnthebaptist" wrote in message
...
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" wrote:

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" wrote in
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?

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" wrote:

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

"johnthebaptist" wrote in message
...
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" wrote:

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" wrote in
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?