Need with formulas

Good day Community,

I have a spreadsheet where I need to know;

  1. From a Date, how many days are: Age at time of approval with Years and Months Column I will need to convert it into how many net days are.
  2. To know in years and months what's the CSPA at the time of approval I have it like this but it gives me decimals =[Age - Pending Days]@row / 365

I hope it makes sence.

thanks in advance :)


  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 05/06/22


    This MIGHT work.

    Example sheet:

    Age formula:

    =NETDAYS([Birth Date]@row, [End Date]@row)

    Years (decimal) formula:

    =(Age@row - [Days Pending]@row) / 365

    Years (flat) formula:

    =ROUNDDOWN([Years (decimal)]@row, 0)

    Days (minus pending) formula:

    =Age@row - ([Years (flat)]@row * 365) - [Days Pending]@row

    Months formula:

    =ROUNDDOWN([Days (minus pending)]@row / 30.41666666666667)

    Age (Years/Months) formula:

    =[Years (flat)]@row + " Years, " + Months@row + " Months"

  • Thank you!!! It helped a lot.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!