• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF function to include with another formula

razaas

Member
Please if someone can help, I have 31 rows in column-A starting at A9 (numbered 1 to 31)and in row 29 to 31 has this formula: =IF(OR(A36="",DAY(EOMONTH($L$4,0))=A36),"",A36+1), that checks the number of days based on referenced date in cell L4 and define the last day of month either 29, 30 or 31.

column-B has this formula: =CHOOSE(WEEKDAY(DATE(YEAR($L$4),MONTH($L$4),$A9),{1,2,3,4,5,6,7}),"sun","mon","tue","wed","thu","fri","sat") that provides the appropriate day of the week depending on referenced date in cell L4.

My problem is if the days of month in column-A are 29 or 30, then the column-B for the blanks cell in column-A giving an error #VALUE!.

I tried to add ‘IF’ function to my above formula for column-B but unable to write the correct formula.

Hope this make sense and I can get some help.


Thanks to all
 

Hui

Excel Ninja
Staff member
Why not use

B9: =if(A9<>"",DATE(YEAR($L$4),MONTH($L$4),$A9),"")

Copy down

and apply a Custom Format to the cells as ddd
 

razaas

Member
Hui, Thanks for the reply, i want to enter first three letters of each weekday(sun=sunday and so on), if that meets the criteria, my formulas are all working well except in last 1 or 2 rows if the days of month are less than 30 days. Hope this is clear for you. Thanks once again.
 

Hui

Excel Ninja
Staff member
The above will return the first 3 letters of each day, using a custom Number format

Ctrl 1, Custom, ddd


otherwise

B9: =if(A9<>"",CHOOSE(WEEKDAY(DATE(YEAR($L$4),MONTH($L$4),$A9),{1,2,3,4,5,6,7}),"sun","mon","tue","wed","thu","fri","sat"),"")
 

razaas

Member
Hui, As usual perfect solutions, both of your formulas working well, with the first formula i need to adjust the column width, the width in my sheet is 5 it works well if i use: =if(A9<>"",CHOOSE(WEEKDAY(DATE(YEAR($L$4),MONTH($L$4),$A9),{1,2,3,4,5,6,7}),"sun","mon","tue","wed","thu","fri","sat"),"").


if i use this: =if(A9<>"",DATE(YEAR($L$4),MONTH($L$4),$A9),"") i need to make my column wider, i do not know why.


Anyway, thanks for great help, please keep up the good work. Have a nice day!
 
Top