Forum Discussion
Validating & Populating Cells with dates based on a Date Picker
Helloepyon,
You can do this entirely with formulas. Assuming your date picker is in A1, the weekday dates go in A2, H2, O2, V2, AC2, and the month names go in D3, K3, R3, Y3, AF3, here’s how:
In A2 put:
=A1-WEEKDAY(A1,2)+1
This calculates the Monday of the week for any date you select.
For the rest of the weekdays use:
H2: =A2+1
O2: =A2+2
V2: =A2+3
AC2: =A2+4
For the month names above each date use:
D3: =TEXT(A2,"mmmm")
K3: =TEXT(H2,"mmmm")
R3: =TEXT(O2,"mmmm")
Y3: =TEXT(V2,"mmmm")
AF3: =TEXT(AC2,"mmmm")
This setup will automatically populate Monday to Friday dates and their corresponding month names based on the date chosen in A1. If you want three-letter month abbreviations, use "mmm" instead of "mmmm".
Official Microsoft documentation for reference:
WEEKDAY function: https://support.microsoft.com/en-us/office/weekday-function-60e44483-2ed1-439f-8bd0-e404c190949a
TEXT function: https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c