I have a sumifs formula that goes like:
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.
Excel Formula:
=sumifs(sheet2!C:C,sheet2!B:B,sheet1!B1,sheet2!A:A,"<="&sheet1!A1,...[plus other criteria])
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.