Find previous date for then calculate days between dates

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

Best Answer

  • Paul H
    Paul H ✭✭✭✭✭✭
    edited 03/08/23 Answer ✓

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

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    edited 03/08/23 Answer ✓

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

  • Thanks for your help. Problem solved!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!