If statement with time

[saltnsnails]

I have a formula that calculates time. It works well. I am trying to have
another formula return a value based on time calculated. If a cell equals 30
minutes I want the formula to return a value of 0. If it does not equal 30,
I would like a value of 1. So I have entered =if(c7=30, 0, 1). C7 refers to
the cell where the time is calculated. I entered time data and the time
calculates a 30 but the other formula is returning a 1. Any thoughts?

 

[Stefi]

Try
=IF(MINUTE(C7)=30,0,1)

Regards,
Stefi

„saltnsnails†ezt írta:

 

[saltnsnails]

Stefi,
Thanks for the reply. I inputed that formula but it is still returning a 1
when the calculated time cell equals 30.

 

[David Biddulph]

Is the C7 cell calculating a number of minutes, or is it a time in Excel
format? What is your formula, and what are the input values to the formula?

What value do you see in C7 if you temporarily change the format from time
to General or Number?

Also, are you sure that the time calculated is exactly 30 minutes, or might
it be 29.9999999 or 30.000001? You may wish to include a ROUND function in
your criterion.

 

[muddan madhu]

Try this

=IF(MINUTE(VALUE(C7))=30,0,1)

Stefi,
Thanks for the reply.  I inputed that formula but it is still returninga 1
when the calculated time cell equals 30.
--
-CRM





- Show quoted text -

 

[jamesgreen55]

Stefi,
Thanks for the reply.  I inputed that formula but it is still returninga 1
when the calculated time cell equals 30.
--
-CRM





- Show quoted text -

perhaps your formula does not resolve to an absolute value of 30?

Try rounding;

30.000158 0 =IF(B6=30,1,0)
30.000158 1 =IF(ROUND(B7,1)=30,1,0)

 

[saltnsnails]

My formula to calculate time in cell C7 looks like this:
=IF(B7>0,(B7-A7)*1440,0)

A7 and B7 are formatted for am/pm time. C7 is formatted for number. I
have 9:00 am in A7 and 9:30 am in B7. It calculates 30. When I ask the if
statement to return a 0 when the statement is true, it returns it as false
instead with a 1.

Hope that makes sense.

 

[saltnsnails]

James, thanks for the reply. It still returns incorrectly. Maybe I need to
round the first calculation of time? How would I round this formula?:
=IF(B7>0,(B7-A7)*1440,0)
Thanks!

 

[jamesgreen55]

Is the C7 cell calculating a number of minutes, or is it a time in Excel
format?  What is your formula, and what are the input values to the formula?

What value do you see in C7 if you temporarily change the format from time
to General or Number?

Also, are you sure that the time calculated is exactly 30 minutes, or might
it be 29.9999999 or 30.000001?  You may wish to include a ROUND function in
your criterion.
--
David Biddulph







- Show quoted text -

My post isn't showing (google) so I've responded to a similar one;

Can you provide 2 samples? What is the column formatted as ? Any
chance it
was translated into text or something of that nature? try wrapping
quotes around
your values....

 

[David Biddulph]

You are calculating a number of minutes, not a time, so you don't want to
use Stefi's suggestion of the MINUTE() function.
With your example values in those cells, the formula =IF(C7=30, 0, 1)
returns zero for me, so I guess you may have some rounding problems. You
didn't answer my questions.

What do you see in C7 if you format temporarily as number with 15 decimal
places? If you see anything different from 30.000000000000000 it may be
interesting to apply the same formatting to A7 and B7 and see if you see
0.375000000000000 and 0.395833333333333.

To tackle any rounding problems, what happens if you try =IF(ROUND(C7,0)=30,
0, 1) ?

 

[Stefi]

I tested your example and =if(c7=30, 0, 1) gave 0 for me (in Excel 2003).

You might check again your data and formats.

Stefi

„saltnsnails†ezt írta:

 

[Fred Smith]

You round a formula by putting Round in the formula, as in:
=IF(B7>0,round((B7-A7)*1440,0),0)

Regards,
Fred

 

[jamesgreen55]

My formula to calculate time in cell C7 looks like this:
=IF(B7>0,(B7-A7)*1440,0)

A7 and B7 are formatted for am/pm time.  C7 is formatted for number.   I
have 9:00 am in A7 and 9:30 am in B7.  It calculates 30.  When I ask the if
statement to return a 0 when the statement is true, it returns it as false
instead with a 1.

Hope that makes sense.
--
-CRM








- Show quoted text -

Well I am at a loss, as when I use your example with the columns
formatted
as you describe, it does resolve to True (0).

Format as Time Format as Time Format As Number Format As Number
=IF(B7>0,(B7-A7)*1440,0) =IF(C7=30,0,1)
9:00:00 AM 9:30:00 AM 30.00 0

You may have a flakey setting somewhere... check your settings for
things
like automatic vs manual calculations (tools>options>calcuations).

Try Exiting Excel... pasting into a new workbook etc.. what version
are you on?

 

[saltnsnails]

I have it figured out. It did return the correct value on certain time
frames but not on others. I think it was b/c of the rounding issue. Some of
the figures came back 30.0000000000001000 etc when I expanded the decimals.
Thanks!

 

[saltnsnails]

James,
Thanks for the response. I am in 2003. The problem was that the
calculating formula may return a value of 30.0000000000010000 but I only saw
the 30. I even checked for decimals orignally but only went out about 6
places. So I rounded the calculation to whole numbers and that made all my
other functions operate properly. Thanks everyone!

 

[Stefi]

A late idea came to my mind: if you temporarily format the cells like
hh:mm:ss then seconds not equaling zero would at once point to the rounding
issue.

Thanks for the feedback!
Stefi


„saltnsnails†ezt írta: