SOLVED

New Contributor

# Select data depening on weekday

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

4 Replies
best response confirmed by HenrikKruse (New Contributor)
Solution

# Re: Select data depening on weekday

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)

# Re: Select data depening on weekday

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

# Re: Select data depening on weekday

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?

# Re: Select data depening on weekday

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
Thank you so much for your help, I was not even sure it could be done.
Thank you