Hi Team,
Hope you are well.
I need help.
I have two date fields.
a) Cntr_creation_date
b) Issue date
My workflow should have a formula tool which can calculate and display records that were entered more than 2 days
(excl. weekends and public holidays) after the Cntr_creation_date. Means the contracts which are entered in the system after 2 days of actually creating the contract.
i think there it should be difference between the Cntr_creation_date and the Issue date.
i have a formula below can you please let me know if this does the same thing,
((DateTimeDiff([ISSUEDATE],[Cntr_creation_date],"days")*5 - (ToNumber(DateTimeFormat([Cntr_creation_date], "%w"))-ToNumber(DateTimeFormat([ISSUEDATE], "%w")))*2) / 7) + IIF(DateTimeFormat([ISSUEDATE], "%w")=="6",-1,0) +
IIF(DateTimeFormat([Cntr_creation_date], "%w")=="0",-1,0)
Thanks
SR
@sriniprad08 the expression calculates the number of business days (working days) between two dates, [ISSUEDATE] and [Cntr_creation_date] and excludes the weekends
Thanks @binuacs so it means i can use the query for my own analysis on (calculating the business days excluding weekends) correct?
@sriniprad08 yes, it only excludes the weekends not the public holidays
Hi@binuacs thanks for the reply. Can you please help me with understanding what each expression does. especially why we multiply by *5 in the first expression
1) ((DateTimeDiff([ISSUEDATE],[Cntr_creation_date],"days")*5 -
2) (ToNumber(DateTimeFormat([Cntr_creation_date], "%w"))-
3) ToNumber(DateTimeFormat([ISSUEDATE], "%w")))*2) / 7) +
4) IIF(DateTimeFormat([ISSUEDATE], "%w")=="6",-1,0) +
5) IIF(DateTimeFormat([Cntr_creation_date], "%w")=="0",-1,0)
@sriniprad08 more tools, but this might be slightly easier to follow