Ageing between 2 date and time

[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

[KGT]

Have you got an attempted method that you've tried? There's a bit of unknown in the below and so it might require a little playing around.

There's a bunch of ways to do this. I would blow the records out to half hours, but most people would only create a row per day. Theory would be to get a table like this:

• Use the generate rows to create a row per day and then some "stuff" to get the table in the format you want.
• Then create a field for Day of Week: DateTimeFormat([StartDate],"%A")
  • For more: https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#specifiers
• Then a column to work out how many hours that day
• Summarise on RecordID to aggregate

[shahnawaz_khan]

hi, is it possible to you to create workflow?

[sagarsiyer]

you can use the below formula 

IF DateTimeFormat([Created On], '%A') = "Sunday" OR DateTimeFormat([Close Date], '%A') = "Sunday" THEN 0ELSEIF DateTimeFormat([Created On], '%H:%M:%S') < "09:30:00" THEN TimeDiff([Close Date], AdjustedCreatedOnForWorkingHours, 'hours') ELSE TimeDiff([Close Date], [Created On], 'hours') ENDIF

[Qiu]

@shahnawaz_khan 
I think we can go this way. but there are 2 records are not consistent with the result you provided.
Kindly check and let me where I did wrong.

[shahnawaz_khan]

Hi Qiu,

I am observing that the values being generated are lower than the uploaded data (insert value - 100301, output value 96588) and in most cases, they are negative and also we can see minus sign 2 digit before the end, like this  -10:33:-20

For reference attaching the input and output data

[Qiu]

@shahnawaz_khan 
Take the first row as sample, 

Start is the morning 9:30 and close is the after midnight of the same date, so it will be minus.
Created On Close Date
2025-01-01 09:30:00 2025-01-01 00:00:18
2025-01-01 09:30:00 2025-01-01 00:03:40

[shahnawaz_khan]

input data is below.                                      

Created On	Close Date
01-01-2025 00:00:16	01-01-2025 00:00:18
01-01-2025 00:00:30	01-01-2025 00:03:40

Output

Created On	Close Date
01-01-2025 09:30:00	01-01-2025 00:00:18
01-01-2025 09:30:00	01-01-2025 00:03:40

Created on date is got converted after using the logics which you shared.  It should come as 00:00:00 when its created date and the closed date fall under non working hours.

[Qiu · Accepted solution]

@shahnawaz_khan 
I have carefully looked the data again and revised the flow.
I am quite confident this time.

😂