Sumifs when criteria column has dates stored as text

[dejoespra]

I have a sumifs formula that goes like:

=sumifs(sheet2!C:C,sheet2!B:B,sheet1!B1,sheet2!A:A,"<="&sheet1!A1,...[plus other criteria])

where:
sheet2!C:C contains dollar values
sheet2!B:B contains account numbers
sheet2!A:A contains dates

My problem is that A:A is not always formatted the same. Sometimes it is a date, other times it is text in the form "mm dd yy". This is due to the fact that sheet2 is a downloaded report that can be downloaded by anyone, and the date format is dictated by the settings in each person's account preferences. How can I still use sumifs (or another formula) without manually changing the data in A:A or using a helper column? I do not want to use a helper column as the people using this sheet aren't all excel savvy and I want them to just copy the report and paste it in sheet2 with no other steps needed.

I've also considered using vba to convert the data, but I'd prefer not to go that route.

 

[Zot]

I guess the best way is to have a macro to check every data in column A and convert data to appropriate format.

If everyone is entering data on a shared workbook, then probably a macro will convert to appropriate format each time after the date data is entered.

Did every person copy data from their workbook to master shared copy or how data entering flow is like? This will help others with solution understand better.

 

[dejoespra]

I guess the best way is to have a macro to check every data in column A and convert data to appropriate format.

If everyone is entering data on a shared workbook, then probably a macro will convert to appropriate format each time after the date data is entered.

Did every person copy data from their workbook to master shared copy or how data entering flow is like? This will help others with solution understand better.

A user will download a report, copy and paste into sheet2.

 

[Zot]

Can you give examples on how the date data variations look like? I see so many possibility like probably 6 digit number with or without colon, etc

 

[dejoespra]

Can you give examples on how the date data variations look like? I see so many possibility like probably 6 digit number with or without colon, etc

1) normal excel date format
2) mm dd yy
There are many possible date formats, but basically these two are the only ones that are likely to show up.

 

[dejoespra]

1) normal excel date format
2) mm dd yy
There are many possible date formats, but basically these two are the only ones that are likely to show up.

Just to clarify, by normal excel date format, I mean date as number (e.g., 1/1/1900 = 1)