SOLVED

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

Copper Contributor

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

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?

6 Replies

# Re: Need help on what formulas to use to able to complete a table

``=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.

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.

# Re: Need help on what formulas to use to able to complete a table

@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

# Re: Need help on what formulas to use to able to complete a table

``=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.

# Re: Need help on what formulas to use to able to complete a table

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?

# Re: Need help on what formulas to use to able to complete a table

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

# Re: Need help on what formulas to use to able to complete a table

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

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

# Re: Need help on what formulas to use to able to complete a table

``=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.