How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6960
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I have an excel 2016 question. I have a spreadsheet with

Customer Question

I have an excel 2016 question. I have a spreadsheet with contract date ranges, e.g. 04/01/2015 to 03/31/2016, 08/01/2016 to 7/31/2016. I need an excel formula to evaluate the beginning and ending dates and calculate how many months have occurred in a certain year, as of a certain date. For example, if the as of date is 09/30/16, then the answer to first date range is 3 months for 2016, and the answer to second date range is 2 months for 2016.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Jess M. replied 1 year ago.

Hi Tim, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you please send me a sample of your Excel file so that I can directly work in the needed formula? Just save a copy of the file, then delete sensitive info, then use the Add Files button to attach it when you reply.

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 1 year ago.

I do not see an add file option,

Expert:  Jess M. replied 1 year ago.

Thank you for the sample file. Give me a moment to check it

Expert:  Jess M. replied 1 year ago.

I got the file and it appears to be straightforward. Now my question is, do you want it to be dynamic by using the TODAY function in column D? That is, when you check or open it by December, it will be calculating FOR December.

Please confirm

Customer: replied 1 year ago.

No, I will supply the as of date in a certain fixed field. Thank you.

Expert:  Jess M. replied 1 year ago.

Ok. In your sample file, let is look at the first row, and that is cell D4. What is the correct computation? Is it 12 months? That is, the end date is 9/30/2015 and it has been 12 months up to this current date. Please confirm if 12 months is correct.

Customer: replied 1 year ago.


















































































ContractStart DateEnd Date months in 2016 at 9/30/16
abc12310/1/20149/30/20150
abc1247/1/20156/30/20166
abc1251/1/201512/31/20150
abc1264/1/20159/30/20150
abc1274/1/20153/31/20163
abc1284/1/20153/31/20163
abc1295/1/20154/30/20164
abc1304/1/20153/31/20163
abc1312/1/20151/31/20161
abc1324/1/20153/31/20163
abc1333/1/20152/29/20162

Expert:  Jess M. replied 1 year ago.

Can you please tell me why the first row is 0? What is the basis of the computation?

Customer: replied 1 year ago.

As of 9/30/2016, zero months of the date range 10/1/14 to 9/30/2015 occurred in the year 2016.

If the as of date were 09/30/2015, the months occurring in 2015 would be 9.

Thank you.

Expert:  Jess M. replied 1 year ago.

Ok. Give me a moment to build the formula for you

Expert:  Jess M. replied 1 year ago.

Please check the formula I created for you:

http://filesxpress.com/d-27e2d390

If there are unwanted results, please let me know so that I can fix it.

Customer: replied 1 year ago.

Jess, please see two cells below. Expected answer is 9, but I get -3 and 3. Please advise. Thank you for your work so far.


























ContractStart DateEnd Date9/30/2016Should BE
abc12X1/1/201612/31/2018 -39
abc12Y1/1/201612/31/2016 39
Expert:  Jess M. replied 1 year ago.

Ok, let me fix this for you

Customer: replied 1 year ago.

HI there Jess, any updates? Thank you.

Expert:  Jess M. replied 1 year ago.

Sorry for the late reply. I will complete the formula in a little while when I am with my computer with the Excel. Thank you

Customer: replied 1 year ago.

Thank you!

Expert:  Jess M. replied 1 year ago.

I'm still working on your Excel file, rebuilding the logic of the formula.

Expert:  Jess M. replied 1 year ago.

Hi Tim,

Here is the download link of the completed file:

http://filesxpress.com/d-29866b53

I hope that helped. If you need further assistance, please let me know so that I can assist you further.

Best regards,

Jess