STOPPING DATA SET AFTER A CERTAIN DATE

@tomtingley Updated the workflow

All comments

@tomtingley Can you check if the result below is correct or not? If not make the changes in the formula tool accordingly

@binu_acs Thanks! This does work.

Would you also be able to show for my second example in the same workflow so both lines come through in the same sheet, one after the other.

"Another example, in the second line, the 'Start Date' is 30th September 2023 and the 'End Date' is 1st October 2025, so I only want to show values for between those dates."

Sorry if this is impossible!

Qiu

@tomtingley
I think we can do it by combination of CrossTab and Transpose.
i also considered the case that Exit Date is null

Hope it works for you.

0323-tomtigley.yxmd

@tomtingley Updated the workflow

@Qiu @binu_acs These both work amazing! Thankyou!

@Qiu @binu_acs Sorry to ask again because you have both been amazing, but is there a way to take the workflow attached, incorporate the 'Start Date' and 'End Date' as mentioned above, but only return rows that show zero's within the start and end date ranges?

eg, Row 1 start date is 1st July 2023 and end date is 31st July 2024, I would want this row to show in the output if there was a zero showing within this date range.

Whereas if there was no zero's within that date range, I wouldn't want to show it.

Hope this makes sense.

Buildings.xlsx

Qiu

@tomtingley
I think it is possible. but I do need you to answer below

1. In the data range, it should only Year and month, it is safe we say it will the first day of that month in order to check it is in the range?

Ex: Mar 25 --> 2025-03-01

2. How should we convert this to a date?
FY 2026

@tomtingley I updated in the same workflow

@binu_acs Thankyou! Let me try.

To answer your question, @Qiu I would ignore FY 2026, 2027 and 2028 for now.

@binu_acs @Qiu - Is it possible to use the attached file to create workflow where any values outside the 'Start Date' and 'End Date' range would have the word 'OUT' written in them?

Buildings.xlsx

@tomtingley in the last workflow the flag 0 records from the filter tool are the OUT records, they don’t satisfy the given conditions

@binu_acs Thanks! This makes sense