Forum Discussion
Validating & Populating Cells with dates based on a Date Picker
Hello, we are working on a really simple delivery schedule for Monday thru Friday. The top row has a cell that is formatted as a short date picker. In choosing a date in that cell, I would like the dates of the 5 numbered cells below for each day of that week to be updated based on the date chosen. For instance, I chose 2/1/26 in the date picker. I would like cells A2, H2, O2, V2, and AC2 to correlate to that date number and automatically populate the proper day of the month. If possible, it would be great if the month names that are in cells D3, K3, R3, Y3, and AF3 would also correlate and update properly.
Any help would be most appreciated, I attached a screenshot to show the layout.
Thank you very much in advance.
1 Reply
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)+1This 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+4For 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
