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

Find more posts tagged with

Sheets

Formulas

Accepted answers

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")

All comments

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

NameEqualsRyan

Thanks for your help. Problem solved!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.