|
If statement with time
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? -- -CRM |
If statement with time
Try
=IF(MINUTE(C7)=30,0,1) Regards, Stefi €žsaltnsnails€ť ezt Ă*rta: 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? -- -CRM |
If statement with time
Stefi,
Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi €žsaltnsnails€ť ezt Ă*rta: 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? -- -CRM |
If statement with time
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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt írta: 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? -- -CRM |
If statement with time
Try this
=IF(MINUTE(VALUE(C7))=30,0,1) On Sep 24, 8:10*am, saltnsnails wrote: Stefi, Thanks for the reply. *I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi „saltnsnails” ezt írta: 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? -- -CRM- Hide quoted text - - Show quoted text - |
If statement with time
On Sep 24, 8:10*am, saltnsnails
wrote: Stefi, Thanks for the reply. *I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi „saltnsnails” ezt írta: 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? -- -CRM- Hide quoted text - - 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) |
If statement with time
My formula to calculate time in cell C7 looks like this:
=IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt Ă*rta: 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? -- -CRM |
If statement with time
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(B70,(B7-A7)*1440,0) Thanks! -- -CRM " wrote: On Sep 24, 8:10 am, saltnsnails wrote: Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi €žsaltnsnails€ť ezt Ă*rta: 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? -- -CRM- Hide quoted text - - 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) |
If statement with time
On Sep 24, 8:52*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. *I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt írta: 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? -- -CRM- Hide quoted text - - 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.... |
If statement with time
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) ? -- David Biddulph "saltnsnails" wrote in message ... My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt írta: 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? -- -CRM |
If statement with time
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: My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt Ă*rta: 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? -- -CRM |
If statement with time
You round a formula by putting Round in the formula, as in:
=IF(B70,round((B7-A7)*1440,0),0) Regards, Fred "saltnsnails" wrote in message ... 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(B70,(B7-A7)*1440,0) Thanks! -- -CRM " wrote: On Sep 24, 8:10 am, saltnsnails wrote: Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi €žsaltnsnails€ť ezt Ă*rta: 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? -- -CRM- Hide quoted text - - 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) |
If statement with time
On Sep 24, 9:11*am, saltnsnails
wrote: My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. *I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt írta: 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? -- -CRM- Hide quoted text - - 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(B70,(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 (toolsoptionscalcuations). Try Exiting Excel... pasting into a new workbook etc.. what version are you on? |
If statement with time
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! -- -CRM "Stefi" wrote: 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: My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt Ă*rta: 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? -- -CRM |
If statement with time
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! -- -CRM " wrote: On Sep 24, 9:11 am, saltnsnails wrote: My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt Ă*rta: 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? -- -CRM- Hide quoted text - - 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(B70,(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 (toolsoptionscalcuations). Try Exiting Excel... pasting into a new workbook etc.. what version are you on? |
If statement with time
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: 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! -- -CRM "Stefi" wrote: 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: My formula to calculate time in cell C7 looks like this: =IF(B70,(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 "David Biddulph" wrote: 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 "saltnsnails" wrote in message ... Stefi, Thanks for the reply. I inputed that formula but it is still returning a 1 when the calculated time cell equals 30. -- -CRM "Stefi" wrote: Try =IF(MINUTE(C7)=30,0,1) Regards, Stefi "saltnsnails" ezt Ă*rta: 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? -- -CRM |
| All times are GMT +1. The time now is 02:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com