Forum Discussion
Solved
MS Excel DAY/WEEK/MONTH Formula.
Hi Everyone!
I want to count the running week days before today date with counting today, when it gets the dates then it count the dates related rows data and count them together and show it to the specific columns/cells,,
such as: today date is 18-09-2022, then needs to count this week dates before today date means the week dates with today will be:
(if weeks first day is Sunday then I have 7 days before today date 18-09-2022)
that means 18-09-2022,17-09-2022,16-09-2022,15-09-2022,14-09-2022,13-09-2022,12-09-2022
(and if weeks first day is Saturday then I have 2 days before today date 18-09-2022)
that means 18-09-2022,17-09-2022
after counting the dates then the next step is to get the dates related date from the data sheet and counting them together in one cell,,,,
I tried to do this somehow but can't count this together,,,
Same thing needs for the MONTH formula,,, Please If any of you can help me to solve this please reply,,, I attached some screen short and an excel sample file for understanding,,,
I have rearranged your formula a little and also developed the one for the month. I think it should fit like this.
=SUMPRODUCT((Data_Sheet[Date and Day]>$C2-WEEKDAY($C2,16))*(Notice_Board[[#Headers],[Renting Cost]]=Data_Sheet[Type of Cost])*Data_Sheet[Cost (TK)])=SUMPRODUCT((Data_Sheet[Date and Day]>$C2-DAY($C2))*(Notice_Board[[#Headers],[Renting Cost]]=Data_Sheet[Type of Cost])*Data_Sheet[Cost (TK)])
I have rearranged your formula a little and also developed the one for the month. I think it should fit like this.
=SUMPRODUCT((Data_Sheet[Date and Day]>$C2-WEEKDAY($C2,16))*(Notice_Board[[#Headers],[Renting Cost]]=Data_Sheet[Type of Cost])*Data_Sheet[Cost (TK)])=SUMPRODUCT((Data_Sheet[Date and Day]>$C2-DAY($C2))*(Notice_Board[[#Headers],[Renting Cost]]=Data_Sheet[Type of Cost])*Data_Sheet[Cost (TK)])
