If formulas

[LouNorris]

Hi - please help. I need to create an IF formula (I think) in column H to say that if a cell in column F contains the number 7, then add 7 days to the date in column A, and likewise if it contains the word "end", then set the date to the 25th of the month in column A. I have attached the spreadsheet I am working on. Please assist if this is possible. Thanks so much.

 

[shrivallabha]

Welcome to Chandoo Org forums.

In cell H3, you can write a formula like below.
=IF(F3="Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(F3="Within 7 days",A3+7,"Formula for other"))

You need to provide logic for values such as "Other (See Notes)" so that you get correct results in all three cases.

 

[LouNorris]

Welcome to Chandoo Org forums.

In cell H3, you can write a formula like below.
=IF(F3="Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(F3="Within 7 days",A3+7,"Formula for other"))

You need to provide logic for values such as "Other (See Notes)" so that you get correct results in all three cases.

thanks so much - will give that a try.

 

[pecoflyer]

Perhaps =IF(ISNUMBER(SEARCH("7",F3)),A3+7,IF(ISNUMBER(SEARCH("end",F3)),DATE(YEAR(A3),MONTH(A3),25),""))

 

[LouNorris]

Welcome to Chandoo Org forums.

In cell H3, you can write a formula like below.
=IF(F3="Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(F3="Within 7 days",A3+7,"Formula for other"))

You need to provide logic for values such as "Other (See Notes)" so that you get correct results in all three cases.

Hi Shrivallabha - I used your formula for this and it worked... however I now have an additional formula to add, and I'm not sure how to do it. One of the IF values is "Next month end" = how can I formulate for A4 to go to the 25th of the following month? Your help is HUGELY appreciated.

 

[shrivallabha]

Hi Lou,

The construct you have implemented is correct, it just needs small tweak. See the red-marked formula to get the next month.

=IF(G3="This Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(G3="Within 7 days",A3+7,IF(G3="Next month end",DATEVALUE("25-"&TEXT(EOMONTH(A3,1),"mmm-yy")),IF(G3="Dep Paid",0))))

 

[LouNorris]

Hi Lou,

The construct you have implemented is correct, it just needs small tweak. See the red-marked formula to get the next month.

=IF(G3="This Month End",DATEVALUE("25-"&TEXT(A3,"mmm-yy")),IF(G3="Within 7 days",A3+7,IF(G3="Next month end",DATEVALUE("25-"&TEXT(EOMONTH(A3,1),"mmm-yy")),IF(G3="Dep Paid",0))))

Thank you so so much. What a huge help.