SOLVED

MS Excel DAY/WEEK/MONTH Formula.

Brass Contributor

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,,,

 

kammumin_1-1663487114764.png

kammumin_0-1663488233041.png

 

 

 

 

2 Replies
best response confirmed by KAM_Mumin (Brass Contributor)
Solution

@KAM_Mumin 

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

 

@dscheikey 

Oh brother I can't believe how easy you make it,,,, thanks a lot,,,,
you can't believe how hard I am going to make it,,, if you see what am I trying to solve this then relay you will impressed and laugh,,,

 

1 best response

Accepted Solutions
best response confirmed by KAM_Mumin (Brass Contributor)
Solution

@KAM_Mumin 

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

 

View solution in original post