Formula for average between 2 dates
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'
Best 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! 😊
Answers
-
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! 😊
-
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
-
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!
-
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?
-
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! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!