# Formula to return day based on month end date & day number of month

1. ## Formula to return day based on month end date & day number of month

Need formulas for cells D4 thru AH4 that return the 1st 3 digits of the day of the week created from the month end date in cell AB2 & the day number of the month in cells E4 thru AH4.

For example, with the date 10/31/07 in cell AB2

___D_|_E_|__F_|_G__|_H_|_I_|_J_|_K__|_L_|_M__|_N_ |_O |_P_|_Q_|,etc
1
2
3
4 Mon|Tue|Wed |Thur|Fri_|Sat|Sun|Mon|Tue|Wed |Thur|Fri_|Sat|Sun|, etc
5__1_|_2_|__3_|__4_|_5_|_6_|_7_|_8_ |_9_|_10_|_11_|12_|13_|14_|,etc

Formula in cell D4 returns Mon because thats the day of 10/01/07
Formula in cell E4 returns Tue because thats the day of 10/02/07
Formula in cell F4 returns Wed because thats the day of 10/03/07
etc

Thanks for all your help. mikeburg

2. Try in D4 copied across

=TEXT(DATE(YEAR(\$AB\$2),MONTH(\$AB\$2),COLUMN(A1)),"ddd")

3. An alternate way and more robust...

Try in D4:

=DATE(YEAR(\$AB\$2),MONTH(\$AB\$2),COLUMN(B1)-COLUMN(\$A\$1)+1)

and in E4 copied across as far as you want....

=IF(MONTH(D4+1)<>MONTH(\$AB\$2),"",DATE(YEAR(\$AB\$2),MONTH(\$AB\$2),COLUMN(B1)-COLUMN(\$A\$1)+1))

and format all cell as Custom: ddd

this will leave cells after the last day of the month blank.

You can then copy those exact formulas down to row 5 and format the row 5 cells as Custom: d to get numbers.

4. Thank you so very, very much! mikeburg

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1