auto fill date and day of week

helpher · March 18, 2021, 2:36pm

Hi there,

I am trying to have a simple formula that shows this:

i have a cell ‘A1’ with the year ‘2021’
i have a cell ‘A2’ with the month ‘January’ (Must be shown as a word not number)
i have a cell ‘A3’ with the day ‘1’

All i am looking for is for ‘B3’ to show the day of week ‘Friday’.

Thank you

JohnSUN · March 18, 2021, 2:46pm

=TEXT(DATEVALUE(CONCAT(A2;" ";A3;", ";A1));"NNN")

JohnSUN · March 18, 2021, 2:57pm

Alternative

=IFERROR(TEXT(DATE(A1;FIND(LEFT(A2;3);" JanFebMarAprMayJunJulAugSepOctNovDec")/3;A3);"NNN");"???")

(there are two spaces before Jan)

helpher · March 18, 2021, 5:11pm

Thank you JohnSUN, the second suggestion does give me what i am looking for. It does seem to think that the 1st of Jan 2021 is Tuesday instead of friday though. Is that easy to correct ?

JohnSUN · March 18, 2021, 5:21pm

Yes, my bad - please replace this “two spaces” to “two dots” and use SEARCH() instead FIND()

=IFERROR(TEXT( DATE(A1; SEARCH(LEFT(A2;3);"..JanFebMarAprMayJunJulAugSepOctNovDec")/3; A3);"NNN");"???")

helpher · March 18, 2021, 5:53pm

Thank you. That is perfect. Just what i am looking for. Shame I cannot tag this as the correct answer.

anon73440385 · March 18, 2021, 2:50pm

Hello

Set A1 to 2021-01-01 and format using Format Code: YYYY
Set A2 to =A1 and format using Format Code: MMMM
Set A3 to =A1 and format using Format Code: D
Set B3 to =A1 and format using Format Code: DDDD

helpher · March 18, 2021, 5:15pm

thank you for your help but this wont work for my use case as i have many months setup on multiple pages and want to be able to just change the year and have all the months and days of week change automatically. that means is is only one cell change. your suggestion means i would need to edit every month. thank you