EOMONTH Nightmare

Hi All,

I'm having an issue with the EOMONTH function in a financial model that I've built, and I don't know what I've done wrongly.

Summary:

• There is a Summary Tab that contains the bulk of the variable inputs for the model. One of these is the "Start Date" for a cashflow

• That start date is a general short date format (for example, 30/05/2021)

• For clarity, let's say that this cell is "Summary Tab C13"

• In the cashflow tab, I have my first month ("Month 1", let's say that's Cell "C1") is a direct reference to the "Start Date" cell in the summary tab

• For clarity: In the Cashflow Tab Cell C1 contains "=Summary Tab C13". This references fine, and so the date under "Month 1"/in "Cell C1" reads "30/05/2021"

The trouble that I am having is that the cashflow is a 10-year cashflow and various other elements of the model refer to specific dates in the cashflow over a 120-month period. For example, the "HLOOKUP" function is used that pulls from the dates. I therefore need the dates to pull through on a monthly basis for 120 months. The way I usually do that is to use the "EOMONTH" function. For example, "=EOMONTH(Start Date;1). This takes the previous month and then adds 1 month onto it. This enables me to be able to change dates in the model dynamically from the Summary Tab. I can change the "Start Date" and then all of the dates in the spreadsheet amend themselves based upon that.

This usually pulls through fine, and it was pulling through fine in this model. However, I have gone into the summary tab and changed the "Start Date" and now all of my "EOMONTH" functions just say "Value!".

For example:

• "Cashflow Tab Cell C1" (="Summary Tab C13") and reflects the correct date: 30/05/2021

• "Cashflow Tab D1" (=EOMONTH(C1;1) and when I press enter it says "Value!"

I have checked the date formatting, ensuring that that all matches up, etc, but it still won't work.

I would love it if someone out there on the Excel Forum could come to my aid and help a frustrated analyst before he either:

1) get's fired for not delivering the model on time; or
2) throws his computer out of the window and runs into the wilderness for a life of more simplicity.

Attach a sample workbook (not image).so that we do not have to manually key in your data to do any testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

What is the actual formula that you have in C1? The one that you quote as your example is not syntactically correct to return the value from that cell, and with you showing quotes then it would just return the string.

If you are getting a #VALUE error, then you probably have a text value that just looks like a date.

Hope this helps.

Pete

Hi All,

I apologise for not responding to any of the messages on my thread - as it turned out, there was a tiny issue embedded in one of the EOMONTH functions in part of the model, which threw everything off.

As it turns out, it's often helpful to go to bed and then look at it again in the morning with fresh eyes!

Thanks for all the help and suggestions - some of it was very useful in me tracking down the culprit!