1

What I would like is a calculation that would take return the value of the Thursday before my [TargetDate] value. If it is on a Thursday, it too has to be the Thursday before. This is to make sure that the form is submitted before the target date.

I found this, but looks like I'd have to do a WEEKDAY statement for every day of the week

How to default a date field to a particular day of the week?

Can't seem to wrap my head around this one! Any help would be greatly appreciated!

Improve this question

1 Answer 1

Reset to default
1

This should get the job done:

=IF(WEEKDAY([TargetDate])<=5,[TargetDate]-WEEKDAY([TargetDate])-2,[TargetDate]-WEEKDAY([TargetDate])+5)
Improve this answer
4
  • Do I put this into a 'calculated' or 'date and time' column? I tried 'calculated' with returned data being single line and date, and 'date and time' with the default view being calculated - they all give me a 'syntax error or not supported' error. The only change I had made was [TargetDate] to [Target Install date] as follows: =IF(WEEKDAY([Target Install date])<=5;[Target Install date]-WEEKDAY([Target Install date])-2;[Target Install date]-WEEKDAY([Target Install date])+5) Where should I put the formula?
    – Michael Anderson
    Commented Dec 15, 2014 at 15:00
  • This has been tested in Excel, so please try it now in calculated column (with comma instead of semicolon). Make sure that in calculated column you set The data type returned from this formula is: Date and Time.
    – Slaven Semper
    Commented Dec 15, 2014 at 15:02
  • Perfect solution! Thank you Slaven! Out of curiosity, would you know how to make the date Wednesday if Thursday is a holiday (like upcoming Christmas!)?
    – Michael Anderson
    Commented Dec 15, 2014 at 15:22
  • Handling holidays is more complex and cannot be solved with calculated column alone. You would have to implement custom code.
    – Slaven Semper
    Commented Dec 15, 2014 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.