Hi, just wondering if it is possible to have numbers calculate the next date of a given day name.
eg. when a weekday name is entered or selected from a popup menu can Numbers return a date the next occurrence.
MacBook Air 13″, macOS 11.4
petergoode wrote:
when a weekday name is entered or selected from a popup menu can Numbers return a date the next occurrence.
You can do this in one cell if you want:
A2 has a Pop-Up Menu with the names of the day of the week.
B2 has this formula:
=TODAY()+MOD(MATCH(A2,{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"},0)−WEEKDAY(TODAY()),7)
SG
petergoode wrote:
when a weekday name is entered or selected from a popup menu can Numbers return a date the next occurrence.
You can do this in one cell if you want:
A2 has a Pop-Up Menu with the names of the day of the week.
B2 has this formula:
=TODAY()+MOD(MATCH(A2,{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"},0)−WEEKDAY(TODAY()),7)
SG
Great thanks for your reply, I will give that a go.
awesome , thank you . Works great.
I can't think of a direct formula or functions that would do it. Here is a way that requires a lookup table and a few columns/cells. There may be a better way.
Table 2 is just what you see. I created a popup from the day names I typed into column A, which is why you see A1 as a popup in the screenshot.
Formulas in Table 1:
B2 is a popup with day names
C2 =MATCH(B2,Table 2::A)
D2 =WEEKDAY(TODAY())
E2 =TODAY()+C2−D2+IF(C2>D2,0,7)
Fill down with all formulas to complete the columns
Hide columns C and D afterward
You can do away with columns C and D if you put their formulas directly into the formula in E, in the two places each where C and D are referenced.
date from given day name
