Database report returns different date formats sometimes,I need to cover both formats(MAC)

THIS IS EXPLICIT FOR MAC, I did not clarify this initially.

I get a CSV file from a reporting system and I take some parts of that data and paste it to another Excel file where a macro uses a formula to get the difference from one date provided from the CSV compared to the date today.

Cell L19 is where the data from the CSV file is going

Some days the data looks like this:

04/03/2016 00:00 , for this, the following formula works > =IF(OR(ISERROR(I19),ISBLANK(I19)),"", DATEDIF(L19,TODAY(),"Y"))

At other days the data looks like > 04/03/2016 10:34 AM and the following formula works:

=IF(OR(ISERROR(I3),ISBLANK(I3)),"", DATEDIF(DATE(RIGHT(SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1),4),LEFT(SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1),FIND("$",SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1))-1),MID(SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1),FIND("$",SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1))+1,FIND("/",SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1))-FIND("$",SUBSTITUTE(LEFT(L3,FIND(" ",L3)-1),"/","$",1))-1)),TODAY(),"Y"))

Is there a way to cover both cases in the same Cell formula?

Do you want a formula or VBA? If the former, I will move the thread for you.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

This should work in both cases:

=IF(OR(ISERROR(I19),ISBLANK(I19)),"", DATEDIF(ABS(L19),TODAY(),"Y"))

It will pull only the date part where a time is also present.

Originally Posted by AliGW

Do you want a formula or VBA? ...

Either will do, VBA or Formula, the whole procedure is based on VBA, this is just one component.

I have uploaded a CSV file to the first post named SourceData.CSV , the Columns A and C are exactly how I get the data, unfortunately, your formula > =IF(OR(ISERROR(I19),ISBLANK(I19)),"", DATEDIF(ABS(L19),TODAY(),"Y"))
did not work with it.

Maybe I should say that I work on a MAC, it might be important.

I have uploaded a CSV file to the first post named SourceData.CSV

Nope - nothing there.

Have you tried my suggestion in post #5?

Please don't quote WHOLE posts - it's just clutter.

Maybe I should say that I work on a MAC, it might be important.

Crucial! Please update your forum profile to add "for Mac" after your Office version information.

Try this:

=DATEDIF(DATE(--MID(A2,FIND(" ",A2)-4,4),--LEFT(A2,FIND("/",A2)-1),--SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")),C2,"Y")

What do you want to happen if the date in column C is BEFORE the one in A?

Maybe this:

=IFERROR(DATEDIF(DATE(--MID(A2,FIND(" ",A2)-4,4),--LEFT(A2,FIND("/",A2)-1),--SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")),C2,"Y"),-DATEDIF(C2,DATE(--MID(A2,FIND(" ",A2)-4,4),--LEFT(A2,FIND("/",A2)-1),--SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")),"Y"))

I edited the main post to say "for MAC"

But I asked you to update your forum profile: Change it from Office 2019 to Office 2019 for Mac, please. Thanks.

@Pete

That's what I thought, but it wasn't working wihtout the --, which is why I added it in.

Dunno - I'm not going to worry, as it wpn't do any harm if it's there, anyway.

We need to hear back from the OP before going any further, anyway.

EDIT: Weird! It IS working now!

For the profile update:

The CSV file I looked at had only one format.

We seem to be back at square one: you don't appear to have tried anything I've suggested, which is a bit disappointing.

However, if you provide a workbook with BOTH date formats included, I'll tell you how to adapt my suggestion. I am NOT working with your unnecessarily long-winded version - sorry.

I asked for an updated workbook showing this.

Please provide the workbook YOU are working with (desensitised) with your attempts in place.

Without this, I am sorry, but I won't be offering any further assistance.

In O3 copied down:

=IFERROR(DATEDIF(DATE(MID(L3,FIND(" ",L3)-4,4),LEFT(L3,FIND("/",L3)-1),SUBSTITUTE(MID(L3,FIND("/",L3)+1,2),"/","")),TODAY(),"Y"),DATEDIF(L3,TODAY(),"Y"))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep. No magic involved!

a whole BI dept could not come up with.

LOL!

I'll explain:

DATE(

MID(L3,FIND(" ",L3)-4,4), - finds the space in the text string and returns the 4 characters before it (year)

LEFT(L3,FIND("/",L3)-1), - finds the first / in the text string and returns the character(s) that preceed it (month)

SUBSTITUTE(MID(L3,FIND("/",L3)+1,2),"/","") - finds the first / in the text string and returns the 2 characters after it - the substitute gets rid of the trailing / if it's a one-digit number (day)

)

The DATE function requires 3 arguments: year, month and day.

Any further questions, just shout.