Sumifs when criteria column has dates stored as text

dejoespra

New Member
Joined
May 22, 2019
Messages
4
I have a sumifs formula that goes like:
Excel Formula:
=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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
973
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 22, 2019
Messages
4
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

Well-known Member
Joined
Nov 26, 2020
Messages
973
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 22, 2019
Messages
4
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

New Member
Joined
May 22, 2019
Messages
4
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top