Skip to content
  • Try for free

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Formula for average between 2 dates

    ✭✭✭
    in Formulas and Functions

    Hi all,

    Probably a very stupid question, but is there a formula that presents the average date between 2 dates. For example, the average date between Monday 4th Septmeber and Wednesday 6th September should be '5th September'

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Best Answer

    • Community Champion
      Answer ✓

      Hi @Jef Snyders,

      The AVG function will do this for you. If on one row you would use:

      =AVG([1st date]@row, [2nd date]@row)

      If you were wanting to do it without any cell references, then you would use AVG & DATE (using your example):

      =AVG(DATE(2023,9,4), DATE(2023,9,6))

      Hope this helps, but if you've any questions or problems then just post! 😊

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      Hi @Jef Snyders,

      The AVG function will do this for you. If on one row you would use:

      =AVG([1st date]@row, [2nd date]@row)

      If you were wanting to do it without any cell references, then you would use AVG & DATE (using your example):

      =AVG(DATE(2023,9,4), DATE(2023,9,6))

      Hope this helps, but if you've any questions or problems then just post! 😊

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi Jef Snyders, 

      By the way, it's quite an interesting question! 

      You just need a helper column ‘Average’ with the formula - 

      =([Date2]@row - [Date1]@row) / 2 

      MicrosoftTeams-image (36).png

      Where Date2 and Date1 are date columns having different dates. 

      Apply the below formula in the ‘Average Date’ column whose column type is a date. 

      =[Date1]@row + Average@row 

      This is how you will get the average date between two dates! 

      MicrosoftTeams-image (37).png


      Thank you,

      Shubham Umale

      Associate - Smartsheet, Ignatiuz Software

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Nick Korna

      Thanks @Nick Korna - I was afraid that this was not going to work as I received this message.

      image.png


      But I should just click on 'allow' and it seems to be ok!

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Shubham

      Hi @Shubham , thanks for your quick response.

      Your solution works as well. But apparently the helper column is not needed as just '=avg' function does work. Any reason why you suggest the helper column?

      · Share on FacebookShare on Twitter
    • Community Champion

      The message is just from the header being set to only allow dates (handy to stop people accidentally entering the wrong thing/making a typo):

      image.png

      Of course, this usually stops people entering formulas as well, but as sheet admin you're able to overwrite this as you found! 😉

      Glad this helped solve your question though! 🙂

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions