Date function to calculate an average between 2 dates

New Contributor

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



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.



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


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_



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