Trying to fill dates with formula

[aferland]

I am trying to fill in the date column with multiple same dates and then change the date after a few rows.

Column A has dates, Column B has names that repeat every day, then more columns with data. I am trying to add the dates with a formula that will add, for example, 80 same dates at a time then 80 next dates and so on for a year. I have tried to drag an entire week of dates, but it only copies the week and does not increment the dates down the column.

I have attached a sample and here is an idea of it.

Screenshot 2023-05-23 104358.jpg

[MrShorty]

Here's how I tend to do something like this.

1a) Manually enter the first date into the desired cells like you currently have in A2:A6.
1b) In A7, enter =A2+1. Copy/paste/fill down as far as needed.
2a) Manually enter the names to repeat like you currently have in B2:B6.
2b) In B7, enter =B2. Copy/paste/fill down as far as needed.

Would something like that work for you?

[MarvinP]

Hi aferland,

Seems like a simple If(Andre) type of formula works for this problem. See the attached.
Andre Test Yearly Schedule.xlsx

[aferland]

That if does work for the manual entry thanks.

Now is there a way to have a list of names in a second sheet and have sheet one automatically build itself for the entire year?

I will upload another sample that is closer to what I need

[aferland]

here is a second sample, it's a lot closer to what I need.

sheet 2 has the reference data for the users
Screenshot 2023-05-23 121104.jpg

I'm looking to build sheet 1 automatically from date1 to date2. Each user has 5 entries they can add per day, with days they don't work.
Screenshot 2023-05-23 121317.jpg

The Xlookups are easy, but how do i get it to build itself with the days marked in x as working days, and make sure it repeats 5 times for every user and every day?

[aferland]

I will post in a new Thread since it's a different question.

I am trying to get sheet1 of this spreadsheet to auto fill from reference data on sheet 2.

Rules are: 1- each user has 5 entries per day
2-Entries auto fill per the scheduled work days
3- 2 last entries for each user/day will be tagged Extra (currently doing manually with =MOD(ROW(),4)=0, then sorting by formatting color and adding the text)
4- I would like to be able to add a user in the Reference Data and have it show up in the first sheet completely configured

sheet 2 has the reference data for the users
Attachment 830670

I'm looking to build sheet 1 automatically from date1 to date2. Each user has 5 entries they can add per day, with days they don't work.
Attachment 830669

The Xlookups are easy, but how do i get it to build itself with the days marked in x as working days, and make sure it repeats 5 times for every user and every day?

[JeteMc]

This proposal utilizes Power Query.
1. Convert the range in A1:J6 into an Excel table
2. Add a column to count rows per day and populate using: =SUM(K1,5)
3. Use the following Advanced Editor code in Power Query to produce the green/white table in columns M:N

Please Login or Register  to view this content.

4. Manually populate cell Q2 with the start date
5. Populate Q3 and down using: =IF(COUNTIFS(Q$2:Q2,Q2)=MAX(Table1[Rows/Day]),SUM(Q2,1),Q2)
6. Populate R2 and down using: =INDEX(Table_Table1[Name],MOD(ROWS(R$2:R2)-1,MAX(Table1[Rows/Day]))+1)
7. Populate S2 and down using: =IF(SUMPRODUCT((Table_Table1[Day]=TEXT(Q2,"dddd"))*(Table_Table1[Name]=R2)),"","Off Shift")
8. Populate T2 and down using: =INDEX(Table1[shift],MATCH(R2,Table1[Name],0))
9. Populate U2 and down using: =INDEX(Table1[Manager],MATCH(R2,Table1[Name],0))
Let us know if you have any questions.

[kvsrinivasamurthy]

In A3

Please Login or Register  to view this content.

In B2

Please Login or Register  to view this content.

In C2

Please Login or Register  to view this content.

In D2 Copied to column E also

Please Login or Register  to view this content.