Weekend condition

[bubblyjai1982]

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 

[PangHC]

@bubblyjai1982 i guess it should not use the datetimenow() where the last part. it cause other yesterday got vary. you should use [COB date] instead.
and if it should keep the date if it not weekend. Ignore the first formula (Option 1 (1/2)). 

[binuacs · Accepted solution]

@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 solution]

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!