SOLVED

Select data depening on weekday

%3CLINGO-SUB%20id%3D%22lingo-sub-2769735%22%20slang%3D%22en-US%22%3ESelect%20data%20depening%20on%20weekday%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769735%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20tricky%20one.%20I'm%20making%20a%20worktime%20management%20excel%20with%20a%20sheet%20for%20each%20month%20and%20a%20base%20data%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20sheet%20will%20have%20the%20base%20data%20for%20a%20given%20person.%20Like%20what%20time%20they%20start%20and%20finish%20work%20on%20a%20Monday%20and%20the%20other%20days%2C%20because%20work%20hours%20will%20be%20different%20each%20day%2C%20depending%20if%20it's%20Monday%20or%20Tuesday%20and%20so%20on.%20(see%20image%20sheet%201%20that%20should%20be%20the%20norm%20time%20of%20a%20given%20person)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20sheet%20(sheet%202)%20will%20be%20January%20showing%20all%20the%20days%20in%20the%20month.%20I%20have%20already%20managed%20to%20make%20it%20auto%20color%20weekends.%3C%2FP%3E%3CP%3ESheet%202%20shows%20the%20norm%20time%2C%20and%20then%20the%20given%20person%20fill%20in%20the%20actual%20time%20for%20the%20days%20(faktisk%20tid).%20Then%20a%20lot%20of%20calculations%20will%20happen%20to%20show%20how%20much%20overtime%20has%20been%20saved%20up.%3C%2FP%3E%3CP%3EI%20would%20like%20the%20row%20next%20to%20a%20Monday%20(in%20this%20example%2004-01-2021)%2C%20to%20automatically%20take%20the%20data%20from%20Monday%20in%20sheet%201.%20The%20issue%20is%20that%20in%20a%20year%20when%20I%20update%20the%20dates%2C%20Monday%20will%20be%20in%20a%20different%20row.%3C%2FP%3E%3CP%3ESo%20somehow%20the%20rows%20in%20sheet%202%20needs%20to%20read%20the%20date%20and%20see%20what%20weekday%20it%20is%20and%20choose%20data%20from%20sheet%201%20depending%20on%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20even%20possible%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20for%20the%20best.%3C%2FP%3E%3CP%3EHenrik%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2769735%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2769942%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20data%20depening%20on%20weekday%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161924%22%20target%3D%22_blank%22%3E%40HenrikKruse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20first%20sheet%20is%20named%20Norm.%3C%2FP%3E%0A%3CP%3EOn%20the%20second%20sheet%2C%20in%20B11%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(TEXT(%24A11%2C%22dddd%22)%2CNorm!%24A%249%3A%24C%2415%2C2%2CFALSE)%3C%2FP%3E%0A%3CP%3EAnd%20in%20C11%3A%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(TEXT(%24A11%2C%22dddd%22)%2CNorm!%24A%249%3A%24C%2415%2C3%2CFALSE)%3C%2FP%3E%0A%3CP%3EFormat%20B11%20and%20C11%20as%20time%2C%20then%20fill%20down.%3C%2FP%3E%0A%3CP%3EIn%20Danish%3A%3C%2FP%3E%0A%3CP%3E%3DLOPSLAG(TEKST(%24A11%3B%22dddd%22)%3BNorm!%24A%249%3A%24C%2415%3B2%3BFALSK)%3C%2FP%3E%0A%3CP%3E%3DLOPSLAG(TEKST(%24A11%3B%22dddd%22)%3BNorm!%24A%249%3A%24C%2415%3B3%3BFALSK)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2772841%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20data%20depening%20on%20weekday%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772841%22%20slang%3D%22en-US%22%3E%3CP%3EAmazing!!%20the%20Danish%20works..%20except%20for%20saturdays%20where%20it%20writes%20%23I%2FT%20in%20the%20cells%20insted%20of%20the%20time%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@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)

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

@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?

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