5 Replies Latest reply on Jul 18, 2018 1:48 PM by Renee Coker

# Need a date formula for calculation field

I need a formula to put in a calculation field that will return the previous Saturday's date relative to the current date in a MM/DD/YYYY format.

Any help is appreciated.

• ###### 1. Re: Need a date formula for calculation field

There' might be a more elegant way to do this but this is what I came up with:

If DATENAME('weekday',[Date]) = 'Monday' THEN DATEADD('day',-1,[Date])

ELSEIF DATENAME('weekday',[Date]) = 'Tuesday' THEN DATEADD('day',-2,[Date])

ELSEIF DATENAME('weekday',[Date]) = 'Wednesday' THEN DATEADD('day',-3,[Date])

ELSEIF DATENAME('weekday',[Date]) = 'Thursday' THEN DATEADD('day',-4,[Date])

ELSEIF DATENAME('weekday',[Date]) = 'Friday' THEN DATEADD('day',-5,[Date])

ELSEIF DATENAME('weekday',[Date]) = 'Saturday' THEN DATEADD('day',-6,[Date])

ELSE [Date]

END

• ###### 2. Re: Need a date formula for calculation field

Would I substitute [Date] with Today()?  I want it always relative to the current date, no specific date field.

• ###### 3. Re: Need a date formula for calculation field

yeah replacing it with today() should do it.

Also, if  on Sunday you want it to stay the current date leave it as is, if you want it to say the previous Sunday's date on Sundays replace ELSE [Date] with  ELSEIF DATENAME('weekday',Today()) = 'Sunday' THEN DATEADD('day',-7,Today())

Please mark as correct if this solved your problem

• ###### 4. Re: Need a date formula for calculation field

It seemed to like the Today(), but it seems to be off by one day. As an example, it shows July 15 as the day ending last week, when it should be the 14th. I assume I'll need to adjust the - numbers in the formula, but do I take them down by 1 so that the first if statement is 0 and the first elseif is -1?

• ###### 5. Re: Need a date formula for calculation field

I fixed it. I have the right date now.

Thank you so much for your help!