SOLVED

Date function to calculate an average between 2 dates

Copper 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

@Dirt_Seller 

 

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.

mathetes_0-1635856815541.png

I display the formulas in column D just for reference. The actual formulas are in B11 and B13.

best response confirmed by allyreckerman (Microsoft)
Solution

@Dirt_Seller 

 

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

 

@mathetes 

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

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Dirt_Seller 

 

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

 

View solution in original post