+ Reply to Thread
Results 1 to 18 of 18

Formulas to only calculate on a specific weekday

  1. #1
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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!
    Ask Excel forum - ASabur.xlsx

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: Formulas to only calculate on a specific weekday

    Please mock up the output.
    Quang PT

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,932

    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. #4
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53
    Quote Originally Posted by bebo021999 View Post
    Please mock up the output.
    Can you explain what you mean by mock up the output?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    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,"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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.
    Please see new attachment.Ask Excel forum (rev,1) - ASabur.xlsx
    Last edited by asabur6; 06-08-2024 at 01:11 PM.

  7. #7
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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.
    Please see new attachment.Ask Excel forum (rev,1) - ASabur.xlsx

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Formulas to only calculate on a specific weekday

    Try this in F9,

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

    __________________________________
    "Feedback is the breakfast of champions"

  9. #9
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    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. #11
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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. #12
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    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. #13
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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. #14
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    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)
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Formulas to only calculate on a specific weekday

    Try the following array formula** in cell F15 on the Help2 sheet:
    Formula: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  17. #17
    Registered User
    Join Date
    08-21-2012
    Location
    East Coast, US
    MS-Off Ver
    365 and Desktop
    Posts
    53

    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. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 07-22-2022, 08:46 AM
  2. Want formulas in one specific range to calculate only on demand
    By fpt264 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2019, 12:15 PM
  3. [SOLVED] how to calculate the weekday from day of the month
    By John19 in forum Excel General
    Replies: 9
    Last Post: 07-12-2015, 05:17 AM
  4. [SOLVED] Calculate weekday end date based on sum of weekday start date and cell value
    By Rerock in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2014, 09:17 AM
  5. How to calculate formulas for specific rows that are linked by an ID
    By Cunner in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-14-2014, 12:44 PM
  6. how to calculate a certain weekday
    By njg2982 in forum Excel General
    Replies: 3
    Last Post: 10-28-2009, 12:02 PM
  7. Calculate WeekDay Values
    By shrekut in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2005, 07:46 PM

Bookmarks

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