Help with converting date using EOMonth formula

[walrus_22]

Hi there, I am working with a dataset of thousands of rows. Each row has a date field. The data I have extracted have 2 types of date fields. One that has "dd/mm/yy hh:mm" and another that has "dd/mm/yyyy hh:mm". I am running a 'EOMonth' formula on the column next to them so that I can bring all values that fall in a month to a particular day. However, when I run the EOMonth next to the format that has "dd/mm/yyyy hh:mm", it is generating a value error. I have attached a copy of a sample spreadsheet.

I am looking for a formula or a method with multiple formulas to easily convert thousands of rows of "dd/mm/yyyy hh:mm" to an end of month date.

Thank you in advance!

[AliGW]

A5, A6 and A7 are not real dates - they are text. If you change the formatting of column A to general, you will see the difference.

Where have these come from? It looks like they are in US format.

[walrus_22]

Thank you for your response. They were exported from google analytics, so that might be why. I know that A5-A7 are not dates, but I am looking for a formula that can manipulate them to change into a date?

[PaulM100]

This should do the trick: =EOMONTH(INT(A2),0)

[AliGW]

OK, however I think you have a more fundamental problem to grapple with first.

If these dates have ALL come from the same source, and that source is one with the US date format, then the likelihood is that ALL dates in column A, even those presenting as correct, are in fact wrong.

07/02 should probably be 02/07 ...

Before we go anywhere with this, go back to the source data and check this, because if we proceed as is, you are going to have a lot of incorrect data, I'd wager!!!

This should do the trick: =EOMONTH(INT(A2),0)

Doesn't address the issue, Paul.

[AliGW]

If you are confident that the dates presenting as dates are CORRECT, then you could try this in B2 copied down:

=EOMONTH(IF(ISTEXT(A2),DATE(MID(A2,FIND("|",SUBSTITUTE(A2,"/","|",2))+1,4),SUBSTITUTE(LEFT(A2,2),"/",""),SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"/","|",1))+1,2),"/",""))+RIGHT(A2,5),A2),0)

[walrus_22]

Thank you! you were right and I needed to fix an underlying issue. I have done that now, I really appreciate your help with this

[AliGW]

Glad to help. Thanks for the rep.