copy date, if cell blank use todays date

[Kevw1]

Hi I have the following working formula, but now need to update it.

=IF(ISBLANK('Raw data'!A2),"",DATE(YEAR('Rawdata'!A2),MONTH('Raw data'!A2),1))​

I am now trying to add todays date rather than a blank value by adapting the formula, I am getting a #Value returned with the updated formula:-

=IF(ISBLANK('Raw data'!A2),TODAY(),DATE(YEAR('Rawdata'!A2),MONTH('Raw data'!A2),1))

anyone have any ideas what I am doing wrong please? I have tried several ways to strip the time from the date, this was the only way it would provide the output needed and was working until I add todays date. I use the output as a base for a second formula hence needing the output format to be the same.

 

[steve the fish]

Rawdata is not the same as Raw Data. Check your formula.

 

[wideboydixon]

You have inconsistent reference to "Raw data" - are you missing a space in the YEAR() reference?

WBD

 

[Kevw1]

Hi thanks for the quick reply's it was a Typo adding the formula to the forum not in the actual formula, "Raw data" is correct, I just copied the formula wrong as working on a different computer, so the problem is still happening.

=IF(ISBLANK('Raw data'!A2),TODAY(),DATE(YEAR('Raw data'!A2),MONTH('Raw data'!A2),1))

The formula works correct if I replace

TODAY() with "" but when changed to Today() this is when I get the #Value , also the TODAY value will need to be MONTH,1 also is this just TODAY(MONTH,1)??

The output is used in an index to match to a monthly forecast.​

_{^{<strike>
</strike>}}​

 

[steve the fish]

Try this:

=EOMONTH(IF('Raw data'!A2="",TODAY(),'Raw data'!A2),-1)+1

 

[steve the fish]

As for why you get a value error. That can only happen should A2 be a text value i think. Cant see any other reason.

 

[Kevw1]

Hi Steve, all working many thanks for your help with this, issue resolved.

I had tried so many ways to strip the date and convert, this has been the best one, with the today date also thanks