Forum Discussion
Date function to calculate an average between 2 dates
I'm struggling to put together a formula that will calculate sales between two dates beginning on January 1, of the current year and "today".
I would like to take the actual year to date sale number and based on the current date project what it would be on day 365.
Let me add just a brief explanation. Calculating the number of days between two dates is quite easy, in fact. So long as a date appears in Excel as a numerical date (i.e. NOT text), it may be displayed as 1/1/21, but underneath is a number (44197 in that case). The number for today is found by using the function
=TODAY()
and for 11/2/21 that underlying number is 44502. So the count of days is a simple subtraction to find the difference between those two numbers.
The rest is straight forward math.
Here's a link to a website you might find useful for this (and, in the future, research into other functions_
https://exceljet.net/glossary/excel-date
3 Replies
Let me add just a brief explanation. Calculating the number of days between two dates is quite easy, in fact. So long as a date appears in Excel as a numerical date (i.e. NOT text), it may be displayed as 1/1/21, but underneath is a number (44197 in that case). The number for today is found by using the function
=TODAY()
and for 11/2/21 that underlying number is 44502. So the count of days is a simple subtraction to find the difference between those two numbers.
The rest is straight forward math.
Here's a link to a website you might find useful for this (and, in the future, research into other functions_
https://exceljet.net/glossary/excel-date
Here you go. I've attached a spreadsheet, and you can use it as is, or modify. Enter YTD sales into cell B5, and the rest will be done for you.
I display the formulas in column D just for reference. The actual formulas are in B11 and B13.
Thank you this helps a lot. I was trying to make it too complicated
