Dynamic start date based on weekday name

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
151
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm having difficulty trying to achieve what i want. I am making a Monthly Time Card, with a pre-configured 6 week format layout as seen in the picture, however i want the correct date (ex: 8/1/23) to populate correctly to the weekday name based off the month in the "Month/Year" cell. I have a working formula that almost achieves this, however it returns ONLY the day of the month, but I would like it to return the entire date (month/day/year) and then ill just format to display only the day of the month. I need the full date for more coding in the worksheet that uses a date, not just day of month. Here is the current formula that is in the first cell in the list (Week #1, Sunday) and then I autofilled (drag-down) the remaining of that column down to Week 6, Saturday with this formula.

Formula: =IF(MONTH(FLOOR(Start_Date-1,7)+ROWS($E$8:E8))=MONTH(Start_Date),DAY(FLOOR(Start_Date-1,7)+ROWS($E$8:E8)),"")
FYI: E8 is the cell to the right of Week #1, Sunday, "Start_Date" range is the cell that contains the month and year, in this example "August 2023"


Screenshot 2023-05-01 103814.png
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
In essence, you would like the first day of the month to line up with the day, and then all then rest of the days to follow?
 
Upvote 0
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about
Excel Formula:
=IF(MONTH(FLOOR(Start_Date-1,7)+ROWS($E$8:E8))=MONTH(Start_Date),FLOOR(Start_Date-1,7)+ROWS($E$8:E8),"")
 
Upvote 1
Solution
try this:

Book4
AB
1Employee
2Month/YearAugust 2023
3
4
5Sunday 
6Monday 
7Tuesday01
8Wednesday02
9Thursday03
10Friday04
11Saturday05
12Sunday06
13Monday07
14Tuesday08
15Wednesday09
16Thursday10
17Friday11
18Saturday12
19Sunday13
20Monday14
21Tuesday15
22Wednesday16
23Thursday17
24Friday18
25Saturday19
26Sunday20
27Monday21
28Tuesday22
29Wednesday23
30Thursday24
31Friday25
32Saturday26
33Sunday27
34Monday28
35Tuesday29
36Wednesday30
37Thursday31
38Friday 
Sheet5
Cell Formulas
RangeFormula
B5:B38B5=IF(ISNUMBER(B4),IF(EOMONTH($B$2,0)<B4+1,"",B4+1),IF(A5=TEXT($B$2,"dddd"),$B$2,""))
 
Last edited:
Upvote 0
try this:

Book4
AB
1Employee
2Month/YearAugust 2023
3
4
5Sunday 
6Monday 
7Tuesday01
8Wednesday02
9Thursday03
10Friday04
11Saturday05
12Sunday06
13Monday07
14Tuesday08
15Wednesday09
16Thursday10
17Friday11
18Saturday12
19Sunday13
20Monday14
21Tuesday15
22Wednesday16
23Thursday17
24Friday18
25Saturday19
26Sunday20
27Monday21
28Tuesday22
29Wednesday23
30Thursday24
31Friday25
32Saturday26
33Sunday27
34Monday28
35Tuesday29
36Wednesday30
37Thursday31
38Friday 
Sheet5
Cell Formulas
RangeFormula
B5:B38B5=IF(ISNUMBER(B4),IF(EOMONTH($B$2,0)<B4+1,"",B4+1),IF(A5=TEXT($B$2,"dddd"),$B$2,""))
in this scenario you must enter the first of the month date value in cell B2. So, the value here is August 1, 2023.
 
Last edited:
Upvote 0
if you don't have 365, this may work:
Book4
AB
1Employee
2Month/YearAugust 2023
3
4
5Sunday 
6Monday 
7Tuesday01
8Wednesday02
9Thursday03
10Friday04
11Saturday05
12Sunday06
13Monday07
14Tuesday08
15Wednesday09
16Thursday10
17Friday11
18Saturday12
19Sunday13
20Monday14
21Tuesday15
22Wednesday16
23Thursday17
24Friday18
25Saturday19
26Sunday20
27Monday21
28Tuesday22
29Wednesday23
30Thursday24
31Friday25
32Saturday26
33Sunday27
34Monday28
35Tuesday29
36Wednesday30
37Thursday31
38Friday 
Sheet5
Cell Formulas
RangeFormula
B5:B38B5=IF(B4<>"",IF(EOMONTH($B$2,0)<B4+1,"",B4+1),IF(A5=TEXT($B$2,"dddd"),$B$2,""))
 
Upvote 0
Thanks for updating your profile.
As you have 365, you could also use this in E8 only
Excel Formula:
=LET(d,FLOOR(Start_Date-1,7)+SEQUENCE(42),IF(MONTH(d)=MONTH(Start_Date),d,""))
Clear all cells below E8 first.
 
Upvote 0
But your original formula will work with earlier version. ;)
i'm never sure of when the newer functions made it to 365. I thought ISNUMBER is pretty new.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top