Forum Discussion
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 firs...
=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.
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?
=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.
- 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?
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
