Previous Topic Next Topic

Help: Return day of the week

Solved/Closed
fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014 - Jun 4, 2014 at 06:51 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 23, 2014 at 11:54 AM
Hello,

I have checklist sort of form that I am printing every month to let them sign for task everyday.

from C3 - AG3 it is numbered 1-31 and underneath that row C4-AG4
Its the day of the week for that month like S M T W T F S repeatedly until the last day of the month.

Is there any way to have a macro where in when you change the month in H1 it wil automatically change the day of the week.

Here is the sample file. http://speedy.sh/n3vuy/Sample.xlsx

Thank you.



1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 5, 2014 at 11:38 AM
Hi Fireburn,

No need for macro here.

Use the following formula in row 4 (paste in C4 and drag to the right):
=IF(OR(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=1,WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=7),"S",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=2,"M",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=4,"W",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=6,"F","T"))))

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014
Jun 6, 2014 at 10:34 AM
Works perfect...

Thank you so much. You're always there to help.. I really appreciate it.

Have a good one.
fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014
Jun 20, 2014 at 02:34 AM
Sorry to bug you again.

How can you make it display SUN MON TUE and so on instead of S M T W T F S.

Thanks.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 23, 2014 at 10:44 AM
Then the formula would look like:
=IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=1,"SUN",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=2,"MON",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=3,"TUE",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=4,"WED",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=5,"THU",IF(WEEKDAY(DATE(YEAR($H$1),MONTH($H$1),C3))=6,"FRI","SAT"))))))

Best regards,
Trowa
fireburn Posts 27 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014
Jun 23, 2014 at 10:54 AM
Thank you. I tried before just changing the letters to ddd format but i don't know how to do the Tuesdays:). Thanks a lot for the help, now it's all good.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jun 23, 2014 at 11:54 AM
Always happy to help you Fireburn! :)