Formula for average between 2 dates

Jef Snyders

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'

Nick Korna

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! 😊

Nick Korna

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! 😊

Shubham

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 

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! 

Thank you,

Shubham Umale

Associate - Smartsheet, Ignatiuz Software

Jef Snyders

https://community.smartsheet.com/discussion/comment/393803#Comment_393803

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

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

Jef Snyders

https://community.smartsheet.com/discussion/comment/393804#Comment_393804

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?

Nick Korna

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

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! 🙂