calculating the datetime difference including weekend

[sriniprad08]

Hi Team,

hope you are well.

i need help. Can you please help me in understanding the below code

((DateTimeDiff([ISSUEDATE],[TIMECREATED],"days")*5 - (ToNumber(DateTimeFormat([TIMECREATED], "%w"))-ToNumber(DateTimeFormat([ISSUEDATE], "%w")))*2) / 7) + IIF(DateTimeFormat([ISSUEDATE], "%w")=="6",-1,0) +
IIF(DateTimeFormat([TIMECREATED], "%w")=="0",-1,0)

Thanks,

Sr

[gawa · Accepted solution]

hi @sriniprad08 As it is a matter of logic(not Designer functionality), only who knows the context of data can answer to your question. I mean, it is not us but your colleague😅

By the way, you can rephrase it simpler by using IN operator.

(DateTimeDiff([ISSUEDATE],[TIMECREATED],"days")*5-(ToNumber(DateTimeFormat([TIMECREATED], "%w"))-ToNumber(DateTimeFormat([ISSUEDATE], "%w")))*2)/7
+
IIF(DateTimeFormat([ISSUEDATE], "%w")in ("0","6"),-1,0)

This expression may be for calculating number of weekdays except for Sunday and Saturday, you said "including weekend" though.

If so, you can do:

1) Create all of days by Generate Row tool
2) Find "Week of Day" for those days by Formula tool

3) Filter out Saturday and Sunday, and count the number of records from T-anchor of Filter tool.

That would give you more readable WF, in my opinion.

[sriniprad08]

Great thank you @gawa . Unfortunately that colleague left :)

[sriniprad08]

@gawa can you please share the workflow?

thanks,

Sr

[sriniprad08]

Hi @gawa 

Can you please share the workflow?

Thanks

Sri

[gawa]

@sriniprad08 Sorry, I didn't save that WF so I cannot retrieve it., but if you have specific concern/question on how to configure tools, I'd be happy to help you. Please let me know if any.