SOLVED

Need help on what formulas to use to able to complete a table

Copper Contributor

Hi,

So i have got some data in the below screenshot that I am wanting to convert to a blank purpose made timesheet (2nd screenshot) in excel.

 

The first blanksheet I have got set up with the first employee no "1123". How can I get this to look up the date and input the total no of hours to quantify each cell Monday to Friday by going of the W/C date column?

TABLE DATA.pngBLANK TABLE DATA.png

6 Replies

@alexjw94 

=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,(Tabelle1!$A2=Tabelle2[Date])*(Tabelle1!$B2=Tabelle2[employee]),0)),"")))))))

You can try this formula. The formula must be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.

complete a table.JPG

 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

blue table.JPG

 

green table.JPG

@OliverScheurich  I forgot to mention that both of these are on 2 separate worksheets within the excel, how do I word that formula to go between the 2 worksheets?

best response confirmed by alexjw94 (Copper Contributor)
Solution

@alexjw94 

=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),"")))))))

Do you want another sheet for employee number 1100? You can copy the original sheet for employee 1123 and then enter employee number 1100 in range B2:B57.

employee 1100.JPG

 

Thanks, that is sorted. MY requirements have changed. I need to go from April to March this year. April 1st was on a Friday. How do I rearrange this so it works from the start of April through to the end of march and how would I word the formula?

@alexjw94 

Can you attach screenshots without sensitive data which show the layout of the data along with the expected result?

I have managed to sort the issue, thanks @OliverScheurich
1 best response

Accepted Solutions
best response confirmed by alexjw94 (Copper Contributor)
Solution

@alexjw94 

=IF(AND(WEEKDAY($A2)=2,C$1="Monday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=3,C$1="Tuesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=4,C$1="Wednesday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=5,C$1="Thursday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=6,C$1="Friday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=7,C$1="Saturday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),IF(AND(WEEKDAY($A2)=1,C$1="Sunday"),INDEX(Tabelle2[hours],MATCH(1,($A2=Tabelle2[Date])*($B2=Tabelle2[employee]),0)),"")))))))

Do you want another sheet for employee number 1100? You can copy the original sheet for employee 1123 and then enter employee number 1100 in range B2:B57.

employee 1100.JPG

 

View solution in original post