# Formulas to only calculate on a specific weekday

1. ## Formulas to only calculate on a specific weekday

Hi,

I would like help with two formulas. Please see attachment.

Help1: one cell to calculate only on Fridays
Help2: one cell to reference other cells only on Fridays

Thank so much!

3. ## Re: Formulas to only calculate on a specific weekday

Maybe on sheet "Help 1", cell C4:

=IF(WEEKDAY(TODAY(),15)=1,SUM(B4,G5)-G4,0)
G4:
=IF(WEEKDAY(TODAY(),15)=1,'Help 2'!F9,0)

on sheet Help 2", cell A9:
=IF(WEEKDAY(TODAY(),15)=1,
IF(WEEKDAY(A1,15)=1,A2,0)+
IF(WEEKDAY(C1,15)=1,C2,0)+
IF(WEEKDAY(E1,15)=1,E2,0)+
IF(WEEKDAY(G2,15)=1,G2,0),0)

4. Originally Posted by bebo021999
Can you explain what you mean by mock up the output?

5. ## Re: Formulas to only calculate on a specific weekday

Help 2 F9:

=IF(WEEKDAY(TODAY(),15)=1,XLOOKUP(TODAY(),A1:G1,A2:G2),"")

Help 1 C4:

=IF(WEEKDAY(TODAY(),15)=1,SUM(B4,G5)-G4,"")

6. ## Re: Formulas to only calculate on a specific weekday

Thanks AliGW!

Your formula returned no value and I realized its because I am checking it today (Saturday). I apologize, I should have realized that I need both values to remain until the following Friday.

7. ## Re: Formulas to only calculate on a specific weekday

Thanks bebo021999!

Your formula returned no value and I realized its because I am checking it today (Saturday). I apologize, I should have realized that I need both values to remain until the following Friday.

8. ## Re: Formulas to only calculate on a specific weekday

Try this in F9,

=XLOOKUP(TODAY()+7-WEEKDAY(TODAY()+1),A1:G1,A2:G2,,-1)

9. ## Re: Formulas to only calculate on a specific weekday

Thanks Haseeb,

But yours returned 4. 4 shouldnt show until June 14.
I need to return 3 and remain 3 until June 14, then return 4, and so on.

see picexcel forum pic - Asabur.JPG

10. ## Re: Formulas to only calculate on a specific weekday

Sorry about that. I mistakenly thought next Friday. This should work.

=XLOOKUP(TODAY()+1-WEEKDAY(TODAY(),15),A1:G1,A2:G2,,-1)

11. ## Re: Formulas to only calculate on a specific weekday

Thanks Haseeb, that worked for sheet Help 2. Can you help me with the formula on Help 1 cell C4? same attachment.

12. ## Re: Formulas to only calculate on a specific weekday

I am confused about Help 1 C4. According to your notes on the sheet, the formula provided in post #5 should work. Please explain if you are looking something else.

13. ## Re: Formulas to only calculate on a specific weekday

The formula she provided for Help 1, C4, did work but I should have clarified that I need the output to remain until the following Friday, just like Help 2 F9 (now F15) does.
Also, can you include additional cells to Help 2 F15 formula?

Ask Excel forum (rev.2) - ASabur.xlsx

Thanks!

14. ## Re: Formulas to only calculate on a specific weekday

Can you redesign your sheet for data entry?

the way you are entering is giving unnecessary calculation to excel. Attached is a one recommended method for better entry as well as calculation.

In a new sheet, one column use for entering date & second column for entering it's values. Then convert them to table format.
Then in a new sheet you can simply use a formula to get desired result very easily.

E2, for current date

=TODAY()

E3, to get current Friday date.

=E2+1-WEEKDAY(E2,15)

Then you can simply use the following formula to get the value.

=SUM(TEXTAFTER(FILTER(Table1[Value],Table1[Date]=E3),"-")+0)

15. ## Re: Formulas to only calculate on a specific weekday

Hi Haseeb,

My sheets are mockups of two of my actual sheets. My actual data is entered on Help2 under each date, in the format of (e.g. Billname - 16.36). I have a list of bills due under each date and total at the top. So I cant have it listed as you suggested in your New Method Entry sheet.

The current calculations are working, I just need the following tweeks:

Help1 C4 - allow to only calculate every Friday and remain until the next Friday. (i.e. on June 7 show total until June 14, then show new total, and so on.)
Help2 F15 - just include the additional cells A8:G7 & A8:G8 to reference in the formula =XLOOKUP(TODAY()+1-WEEKDAY(TODAY(),15),A1:G1,A2:G2,,-1)

Thanks!

16. ## Re: Formulas to only calculate on a specific weekday

Try the following array formula** in cell F15 on the Help2 sheet:
Formula:
`Please Login or Register  to view this content.`

For the 365 version of Excel I believe that you can activate by just pressing the Enter key.
For earlier versions of Excel you may have to follow these instructions: **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

17. ## Re: Formulas to only calculate on a specific weekday

Thank you JeteMc!

That formula worked for my array. I no longer need a formula for Help1 C4.

Thank you everyone!

18. ## Re: Formulas to only calculate on a specific weekday

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1