Working hours Ageing

[shahnawaz_khan]

Hi,

I am trying to pull the ageing between one date & time to another with below details.

1. if 9:30am to 7pm is the working hours rest we can consider.

2. Anything between 7pm to 9:30am Ageing will become 0:00:00

3.Sunday is non working day, so anything between Saturday 7pm to Monday 9:30am will also be 0:00:00

Sample : 

Created On	Close Date	Actual Ageing	Ageing in total Hours (this is what I want)
01-01-2025 09:34	04-01-2025 19:05	81:30:53	37:55:53
01-01-2025 13:24	05-01-2025 18:29	101:04:26	37:29:20

[Yoshiro_Fujimori]

Hi @shahnawaz_khan ,

I tried to follow your logic and got the following results, which is slightly different from your expected result.

RecordID	Created On	Close Date	totalHours
1	2025-01-01 09:34:00	2025-01-04 19:05:00	37:56:00
2	2025-01-01 13:24:00	2025-01-05 18:29:00	34:06:00

As 2025-01-05 is Sunday, the end time is "2025-01-04 19:00:00" for both rows, while the start time is 3+ hours late for the 2nd row.

So I guess my result is correct.

Please let me know if you find any issues here. I hope this helps.

Workflow

Generate Rows

Initialization Expression: Left([Created On], 10)

Condition Expression: [date] <= Left([Close Date], 10)

Loop Expression: DateTimeAdd([date], 1, "day")

Formula #1

[isSunday] = DateTimeFormat([date],"%a") = "Sun"

For [secondsInRange], the expression is long, but the idea is;

  - if the date is Sunday, set 0

  - if the date is start date or end date, adjust the time

  - otherwise set seconds from 9:30 to 19:00

Formula #2

[hh] = FLOOR([secondsInRange] / 3600)

[mm] = FLOOR([secondsInRange] / 60) - [hh] * 60

[ss] = [secondsInRange] - [hh] * 3600 - [mm] * 60

[totalHours] = ToString([hh]) + ":" + PadLeft(ToString([mm]), 2, "0") + ":" + PadLeft(ToString([ss]), 2, "0")