Select data depening on weekday

[HenrikKruse]

This is a tricky one. I'm making a worktime management excel with a sheet for each month and a base data sheet.

The first sheet will have the base data for a given person. Like what time they start and finish work on a Monday and the other days, because work hours will be different each day, depending if it's Monday or Tuesday and so on. (see image sheet 1 that should be the norm time of a given person)

The next sheet (sheet 2) will be January showing all the days in the month. I have already managed to make it auto color weekends.

Sheet 2 shows the norm time, and then the given person fill in the actual time for the days (faktisk tid). Then a lot of calculations will happen to show how much overtime has been saved up.

I would like the row next to a Monday (in this example 04-01-2021), to automatically take the data from Monday in sheet 1. The issue is that in a year when I update the dates, Monday will be in a different row.

So somehow the rows in sheet 2 needs to read the date and see what weekday it is and choose data from sheet 1 depending on that.

Is that even possible? 

Hoping for the best.

Henrik

[#2 · Accepted solution]

@HenrikKruse 

Let's say the first sheet is named Norm.

On the second sheet, in B11, enter the formula

=VLOOKUP(TEXT($A11,"dddd"),Norm!$A$9:$C$15,2,FALSE)

And in C11:

=VLOOKUP(TEXT($A11,"dddd"),Norm!$A$9:$C$15,3,FALSE)

Format B11 and C11 as time, then fill down.

In Danish:

=LOPSLAG(TEKST($A11;"dddd");Norm!$A$9:$C$15;2;FALSK)

=LOPSLAG(TEKST($A11;"dddd");Norm!$A$9:$C$15;3;FALSK)

[#3]

Amazing!! the Danish works.. except for saturdays where it writes #I/T in the cells insted of the time?

[#4]

@HenrikKruse 

Strange...

I have attached my test workbook. It won't work "as is" because I used the English names of the days of the week. What happens if you enter the Danish names of the days of the week in A9:A15 on the Norm sheet?

[#5]

I tested with your test workbook, and got som interesting results.
It was because I had left a blank space after saturday in the Norm sheet.
So I just had to delete the space and now it works :D
Thank you so much for your help, I was not even sure it could be done.
Thank you