IF function to include with another formula

[razaas]

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]

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]

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]

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]

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!