Formula to calculate the next Friday after today

I have the formula below in Excel that calculates the next friday from today's date; so that any time the sheet is opened before a friday it will automatically insert the date for the coming friday, and when that date has passed and the sheet is re opened it will automatically insert the next fridays date in the week ending box.

 

 

=TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>4,11,4)

 

Any suggestions how I can replicate this in Smartsheet?

 

Thanks,
Marc

Best Answer

Answers

  • Etienne Desbiens
    Answer ✓

    Here :
    =TODAY() + (6 - WEEKDAY(TODAY()))

    Have a nice day,

     

    Étienne Desbiens

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Marc,

     

    What is supposed to be displayed when opened on Friday or Saturday? Last week or next week's Friday?

     

    The WEEKDAY function returns a 1 for Sunday, regardless of which day is the start of your week (which is configurable in the Project Settings)

    I consider that a good thing for this case but has caused problems when dealing with non-Sunday starting customers for other situations.

     

    I believe this solution will return July 8th on the 8th and 9th of this week.

     

    Craig

  • Hi Craig,

     

    Thanks for your comment; I'm OK with today (Friday) showing today's date (in this case 8th).  TAs the sheet is unlikley to be used at a weekend I think it will be OK.  However, I plan to monitor it just make sure all works OK for me.  The sheet I have a basic agenda for a weekly action call that takes place each Firday - in order that I can minimise my effort in maintaining and distributing it I have a weekly schedule to send the agenda out the day before the meeting and rather than update the date each week I wanted a formula to do that for me - it's just one less thing for me to do :)

     

    So far so good.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    No problem.

    I like to help.

    Etienne's solution will improve a sheet I'm working on for a client.

     

    Craig

     

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!