Formula for average between 2 dates

Jef Snyders · 09/04/23

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 · 09/04/23

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 · 09/04/23

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 · 09/04/23

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 · 09/04/23

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 · 09/04/23

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 · 09/04/23

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

Formula for average between 2 dates

Best Answer

Answers

Help Article Resources

Categories