| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003; using a date in a formula
In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to
warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
hi
2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Hi,
Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
hi
i just read down a list of available dialog boxes(258. vb help claims there ar 794) and i didn't see a "if dialog" box. could you elaborate? I'm confused. the formula work in tests and you claim it's working for you. so i don't understand this "invalid" part. regards FSt1 "Eucalypta" wrote: Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Hi,
By the if dialog box, I meant the box that opens when i click fx in the task bar. If I knew how, I would upload a screenprint. I subscribe to your confusion. Have a nice weekend, I will be back on Monday. KG, Eucalypta A smile will carry you around the world "FSt1" wrote: hi i just read down a list of available dialog boxes(258. vb help claims there ar 794) and i didn't see a "if dialog" box. could you elaborate? I'm confused. the formula work in tests and you claim it's working for you. so i don't understand this "invalid" part. regards FSt1 "Eucalypta" wrote: Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
hi
that seems to be the insert function dialog box. Not on my list so it must be part of the other 974. but i just repeated tests and called up the insert function wizard and i didn't get a invalid formula. works fine on this end. and per you previous post your end too. don't know what to say. regards FSt1 "Eucalypta" wrote: Hi, By the if dialog box, I meant the box that opens when i click fx in the task bar. If I knew how, I would upload a screenprint. I subscribe to your confusion. Have a nice weekend, I will be back on Monday. KG, Eucalypta A smile will carry you around the world "FSt1" wrote: hi i just read down a list of available dialog boxes(258. vb help claims there ar 794) and i didn't see a "if dialog" box. could you elaborate? I'm confused. the formula work in tests and you claim it's working for you. so i don't understand this "invalid" part. regards FSt1 "Eucalypta" wrote: Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
What separator do your windows regional settings call for? Is it looking
for semi-colons instead of commas? -- David Biddulph "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Hi David,
semi-colons is the regional setting. Does this in anyway influence the formula? KG, Eucalypta -- A smile will carry you around the world "David Biddulph" wrote: What separator do your windows regional settings call for? Is it looking for semi-colons instead of commas? -- David Biddulph "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. KG, Eucalypta -- A smile will carry you around the world "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Yes. If your regional settings specify semi-colons as the list separator,
then the list of parameters in a formula will need to be separated by semi-colons instead of by commas. -- David Biddulph "Eucalypta" wrote in message ... Hi David, semi-colons is the regional setting. Does this in anyway influence the formula? "David Biddulph" wrote: What separator do your windows regional settings call for? Is it looking for semi-colons instead of commas? "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Using semi-colons does not seem to make a difference. Below the formula I
have typed: =AND(ISNUMBER(B5);TODAY()<=B5) Any other ideas? KG, Eucalypta -- A smile will carry you around the world "David Biddulph" wrote: Yes. If your regional settings specify semi-colons as the list separator, then the list of parameters in a formula will need to be separated by semi-colons instead of by commas. -- David Biddulph "Eucalypta" wrote in message ... Hi David, semi-colons is the regional setting. Does this in anyway influence the formula? "David Biddulph" wrote: What separator do your windows regional settings call for? Is it looking for semi-colons instead of commas? "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
Hi, please ignore my earlier response. I had made a type error in the
formula. thanks for all the help, though. KG, Eucalypta -- A smile will carry you around the world "David Biddulph" wrote: Yes. If your regional settings specify semi-colons as the list separator, then the list of parameters in a formula will need to be separated by semi-colons instead of by commas. -- David Biddulph "Eucalypta" wrote in message ... Hi David, semi-colons is the regional setting. Does this in anyway influence the formula? "David Biddulph" wrote: What separator do your windows regional settings call for? Is it looking for semi-colons instead of commas? "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003; using a date in a formula
What result did you get from Excel when you tried that?
What results do you get from =ISNUMBER(B5) and from =TODAY()<=B5? What date is in B5? -- David Biddulph "Eucalypta" wrote in message ... Using semi-colons does not seem to make a difference. Below the formula I have typed: =AND(ISNUMBER(B5);TODAY()<=B5) Any other ideas? "David Biddulph" wrote: Yes. If your regional settings specify semi-colons as the list separator, then the list of parameters in a formula will need to be separated by semi-colons instead of by commas. -- David Biddulph "Eucalypta" wrote in message ... Hi David, semi-colons is the regional setting. Does this in anyway influence the formula? "David Biddulph" wrote: What separator do your windows regional settings call for? Is it looking for semi-colons instead of commas? "Eucalypta" wrote in message ... Hi, Thanks for the formula. At first glance Excel does not have any problems with it and it returns with the warning. However, when I open the IF-dialog box Excel tells me the formula is invalid. "FSt1" wrote: hi 2 months is about 60 days....put this in Q5... =IF(AND(B5<(NOW()-60),L5=""),"warning!!!","") regards FSt1 "Eucalypta" wrote: In cel B5 I have a date format; in cel L5 an ordernumber. I want Excel to warn me if the date is 2 months (or more) later and if cel L5 is empty. this warning will be shown is a different cell, i.e. Q5. I am having trouble putting this in a formula or in conditional formatting. Can anyone help me out? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Validation - Excel 2003 | Excel Discussion (Misc queries) | |||
| date format in Excel 2003 | Excel Worksheet Functions | |||
| date format in Excel 2003 | Excel Discussion (Misc queries) | |||
| Excel 2003 formula to calculate date | Excel Worksheet Functions | |||
| In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) | |||
Linear Mode
