Weekend condition

Question

Hi All

Need assistance !!

I'm looking to arrive at good business day, for example I have Date column with yesterday date (COB Date) and want to put a condition stating.

"If COB date is Saturday or Sunday then it should appear Friday else COB Date"

Note: COB date is nothing but yesterday date.

Using Formula Tool I have tried this but not able to extract accurate condition.

IF DateTimeFormat(DateTimeAdd([COB Date],-1,"days"),"%A") = "Saturday"
THEN
DateTimeAdd([COB Date],-2,"days")
ELSEIF
DateTimeFormat(DateTimeAdd([COB Date],-1,"days"),"%A") = "Sunday"
THEN
DateTimeAdd([COB Date],-2,"days")
ELSE
DateTimeAdd(datetimenow(),-1,"days")
ENDIF

Thanks

binu_acs · Accepted Answer

@bubblyjai1982 If COB date is yesterday’s date then you don’t need to -1 days from COB date

IF DateTimeFormat([COB Date],"%A") = "Saturday"
THEN
DateTimeAdd([COB Date],-1,"days")
ELSEIF
DateTimeFormat((COB Date],"%A") = "Sunday"
THEN
DateTimeAdd([COB Date],-2,"days")
ELSE
[COB Date]
ENDIF

davidskaife · Accepted Answer

Hi @bubblyjai1982

If COB Date is already 'yesterday', why are you taking a further day off it using DateTimeAdd? If all you want to do is if the date is a Saturday or Sunday then move it back to the Friday then try this instead

IF DateTimeFormat([Date],"%A") = "Saturday"
THEN
DateTimeAdd([Date],-1,"days")
ELSEIF
DateTimeFormat([Date],"%A") = "Sunday"
THEN
DateTimeAdd([Date],-2,"days")
ELSE
[Date]
ENDIF

If this isn't right what's the error/incorrect output you're getting currently, and providing an example input and expected output would be helpful!