Find previous date for then calculate days between dates

NameEqualsRyan

Hi all,

I'm having difficulty creating a column that shows the number of days between jobs at the specific venue.

Sheet is essentially a list of jobs at various venues.

VENUE A, 02/02/23

VENUE B, 10/02/23

VENUE C, 15/02/23

VENUE A, 16/02/23

The column I'd like to create would show the last row for VENUE A as 14 days.

I'd prefer to be able to create a column formula, which means I can set and forget. I have no issue creating multiple helper columns and I have a master file in another sheet that could be used.

A potential problem is there is full year of jobs listed, but the purpose is to be able to know the new gap between jobs when changing a date and I don't want to alter the future planning on the sheet.

Thanks

Ryan

Paul H

If I am understanding right this should work.

Last Job =MAX(COLLECT([Job Date]:[Job Date], [Job Date]:[Job Date], MAX(COLLECT([Job Date]:[Job Date], Venue:Venue, Venue@row, [Job Date]:[Job Date], <[Job Date]@row, Venue:Venue, Venue@row))))

Days Since Previous Job =IFERROR(NETDAYS([Last Job]@row, [Job Date]@row), "First Job")

Nested into a single formula

Day Since Previous Job =IFERROR(NETDAYS(MAX(COLLECT([Job Date]:[Job Date], [Job Date]:[Job Date], MAX(COLLECT([Job Date]:[Job Date], Venue:Venue, Venue@row, [Job Date]:[Job Date], <[Job Date]@row, Venue:Venue, Venue@row)))), [Job Date]@row), "First Job")

Paul H

If I am understanding right this should work.

Last Job =MAX(COLLECT([Job Date]:[Job Date], [Job Date]:[Job Date], MAX(COLLECT([Job Date]:[Job Date], Venue:Venue, Venue@row, [Job Date]:[Job Date], <[Job Date]@row, Venue:Venue, Venue@row))))

Days Since Previous Job =IFERROR(NETDAYS([Last Job]@row, [Job Date]@row), "First Job")

Nested into a single formula

Day Since Previous Job =IFERROR(NETDAYS(MAX(COLLECT([Job Date]:[Job Date], [Job Date]:[Job Date], MAX(COLLECT([Job Date]:[Job Date], Venue:Venue, Venue@row, [Job Date]:[Job Date], <[Job Date]@row, Venue:Venue, Venue@row)))), [Job Date]@row), "First Job")

NameEqualsRyan

Thanks for your help. Problem solved!