New Contributor

# 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.

3 Replies

# Re: Date function to calculate an average between 2 dates

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.

# Re: Date function to calculate an average between 2 dates

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

# Re: Date function to calculate an average between 2 dates

Thank you this helps a lot.  I was trying to make it too complicated