Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Hi All
I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Trevor --
You need to construct nested IF statements differently, with each one usually set as the "if-false" part of the prior one. I can't figure out what you're trying to accomplish specifically, but it should look something like this: =IF(TODAY()A13,"Overdue",IF(TODAY()C13,"CLOSED", "OPEN")) HTH "trevor" wrote: Hi All I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Your IF construct was not correct
Try this revision: =IF(COUNT(A13,C13)<2,"",IF(TODAY()A13,"OVERDUE",I F(TODAY()C13,"CLOSED","OPEN"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "trevor" wrote: Hi All I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
You were getting an error because of a slight arrangement issue in
your formula: =IF(TODAY()A13,"OVERDUE",IF(TODAY()C13,"CLOSED", "OPEN")) Remember that the second IF has to be one of the results of the first IF. Sorry, but I didn't understand the second part of your question. On May 29, 10:23 am, trevor wrote: Hi All I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Hi max
Thanks for your revised statement. However I did not understand it properly a little confussed! Especially the count bit! I am try to auto input the following: Overdue to appear if the date is after the agreed date Open to appear if within the agreed date closed if data is in any of the closed colums (3) but offten it is entered the 'closed date colum' I want to appear in the either closed, open or overdue "Max" wrote: Your IF construct was not correct Try this revision: =IF(COUNT(A13,C13)<2,"",IF(TODAY()A13,"OVERDUE",I F(TODAY()C13,"CLOSED","OPEN"))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "trevor" wrote: Hi All I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Hi
Thank you for you relpy, I can believe its a little confussing to understand.(Sometimes I find it difficult!) However I am trying to develop a system which auto - inputs the following outputs: OPEN& OVERDUE are entried in the same column! A13 Closed information is entried in another column C13 For example I have this statement - =IF(TODAY()Q16, "OVERDUE", "OPEN") which works (abit), however only for data entered in the cell Q16, but if I add an if statement for the 'CLOSED' then it does not work!? eg:=IF(TODAY()Q16, "OVERDUE", "OPEN", IF(TODAY()C13,"CLOSED"))) - DOES NOT WORK! The statement that you sent was kind of correct, but the open part of the staement didn't! =IF(TODAY()A13,"Overdue",IF(TODAY()C13,"CLOSED", "OPEN")) )Yours! WHICH WORKS BUT THE OPEN DONT NOT APPEAR! HOPE U CAN DO YOUR MAGIC! REGARDS "pdberger" wrote: Trevor -- You need to construct nested IF statements differently, with each one usually set as the "if-false" part of the prior one. I can't figure out what you're trying to accomplish specifically, but it should look something like this: =IF(TODAY()A13,"Overdue",IF(TODAY()C13,"CLOSED", "OPEN")) HTH "trevor" wrote: Hi All I am writing a formula to return 3 values, using 2 IF statement. =IF(TODAY()A13,"OVERDUE","OPEN"),IF(TODAY()C13," CLOSED") But for some reason it keeps on returning an error message #value! Cell A13 & C13 contains dates, formmatted! Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008 Also I have two other cell which can determin the closed date, which you enter the either a two figure number or another cell with a year date (ie: 2007-2008) Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
The front IF check:
=IF(COUNT(A13,C13)<2,"", ... is just an added precaution to ensure that *both* A13 and C13 do contain real dates (real dates are numbers) before proceeding with the rest of the IF evaluations, otherwise spurious/meaningless results might arise since blank cell/s are evaluated as zeros in expressions. For example, if A13 was blank, then: IF(TODAY()A13 would always evaluate to TRUE, which would be a spurious/meaningless result, no? Similarly for C13 & the check: IF(TODAY()C13. When you plugged the entire expression in, did it return the expected results? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "trevor" wrote: Hi max Thanks for your revised statement. However I did not understand it properly a little confussed! Especially the count bit! I am try to auto input the following: Overdue to appear if the date is after the agreed date Open to appear if within the agreed date closed if data is in any of the closed colums (3) but offten it is entered the 'closed date colum' I want to appear in the either closed, open or overdue |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
Thank again for your reply, but i'm still getting no joy with this,
Maybe I am explaining it wrongly to you! Now when I try your statement its outcome is nothing, blank. But I understand your way abit clearer, however is they any chance of getting a contact add so I can show what I am doing? thanks "Max" wrote: The front IF check: =IF(COUNT(A13,C13)<2,"", ... is just an added precaution to ensure that *both* A13 and C13 do contain real dates (real dates are numbers) before proceeding with the rest of the IF evaluations, otherwise spurious/meaningless results might arise since blank cell/s are evaluated as zeros in expressions. For example, if A13 was blank, then: IF(TODAY()A13 would always evaluate to TRUE, which would be a spurious/meaningless result, no? Similarly for C13 & the check: IF(TODAY()C13. When you plugged the entire expression in, did it return the expected results? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "trevor" wrote: Hi max Thanks for your revised statement. However I did not understand it properly a little confussed! Especially the count bit! I am try to auto input the following: Overdue to appear if the date is after the agreed date Open to appear if within the agreed date closed if data is in any of the closed colums (3) but offten it is entered the 'closed date colum' I want to appear in the either closed, open or overdue |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date - Help
If the outcome of Max's formula is a blank, then you haven't got real dates
in your 2 input cells. Check your data. -- David Biddulph "trevor" wrote in message ... Thank again for your reply, but i'm still getting no joy with this, Maybe I am explaining it wrongly to you! Now when I try your statement its outcome is nothing, blank. But I understand your way abit clearer, however is they any chance of getting a contact add so I can show what I am doing? thanks "Max" wrote: The front IF check: =IF(COUNT(A13,C13)<2,"", ... is just an added precaution to ensure that *both* A13 and C13 do contain real dates (real dates are numbers) before proceeding with the rest of the IF evaluations, otherwise spurious/meaningless results might arise since blank cell/s are evaluated as zeros in expressions. For example, if A13 was blank, then: IF(TODAY()A13 would always evaluate to TRUE, which would be a spurious/meaningless result, no? Similarly for C13 & the check: IF(TODAY()C13. When you plugged the entire expression in, did it return the expected results? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "trevor" wrote: Hi max Thanks for your revised statement. However I did not understand it properly a little confussed! Especially the count bit! I am try to auto input the following: Overdue to appear if the date is after the agreed date Open to appear if within the agreed date closed if data is in any of the closed colums (3) but offten it is entered the 'closed date colum' I want to appear in the either closed, open or overdue |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Make date change in excel to current date when opening daily? | Excel Discussion (Misc queries) | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |