Forum Discussion
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
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)
4 Replies
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)
Amazing!! the Danish works.. except for saturdays where it writes #I/T in the cells insted of the time?
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?
