Date function to calculate an average between 2 dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2913728%22%20slang%3D%22en-US%22%3EDate%20function%20to%20calculate%20an%20average%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913728%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20struggling%20to%20put%20together%20a%20formula%20that%20will%20calculate%20sales%20between%20two%20dates%20beginning%20on%20January%201%2C%20of%20the%20current%20year%20and%20%22today%22.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20take%20the%20actual%20year%20to%20date%20sale%20number%20and%20based%20on%20the%20current%20date%20project%20what%20it%20would%20be%20on%20day%20365.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2913728%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2913778%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20function%20to%20calculate%20an%20average%20between%202%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1203518%22%20target%3D%22_blank%22%3E%40Dirt_Seller%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20you%20go.%20I've%20attached%20a%20spreadsheet%2C%20and%20you%20can%20use%20it%20as%20is%2C%20or%20modify.%20%26nbsp%3BEnter%20YTD%20sales%20into%20cell%20B5%2C%20and%20the%20rest%20will%20be%20done%20for%20you.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1635856815541.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323112i142FC88330662741%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1635856815541.png%22%20alt%3D%22mathetes_0-1635856815541.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20display%20the%20formulas%20in%20column%20D%20just%20for%20reference.%20The%20actual%20formulas%20are%20in%20B11%20and%20B13.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

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

@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