Ageing between 2 date and time

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

Ageing report.xlsx

Help

Accepted answers

Qiu

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

😂

0109-shahnawaz_khan-r1.yxzp

All comments

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.

0109-shahnawaz_khan.yxzp

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

Input.xlsx

Output.xlsx

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

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

😂

0109-shahnawaz_khan-r1.yxzp

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335