Insert date in format different than one in regional settings

[Leferty]

Hello!


Apologizes if that's answered somewhere, but I failed to find straight information on that.


Issue:
We've got excel spreadsheet that's being shared between different people using different regional settings, but we all update it with same parsed data, having one strict date format.
We'd of course want to filter table with that data by dates. We also have pivot tables to verify different values over time.


Data we export from external tool, and paste into excel have "dd/mm/yyyy" format (sometimes also dd/mm/yyyy hh:mm, but let's start from dd/mm/yyyy).
Some of us have same date format in regional settings, and everything is smooth for them, some use yyyy-mm-dd format though, and pasted date will be not recognized as a date.
So even if cell format is set to custom - "dd/mm/yyyy", it'll not accept date if I'll past it in format that's not same as one in my regional settings.


Help?


That probably doesn't matter, but we're currently on Office 2013 Pro


Thanks in advance

 

[SQUIDD]

Not sure if this will work for you or how you wish to go about converting.

BUT

have you tried text to columns in the data tab along the top, got me over a few issues.

dave

 

[Leferty]

Not sure how that'd help me out. What I'd want excel to do, is to accept dd/MM/yyyy date format into custom dd/MM/yyyy format cell, even if my regional date settings are different than that.
I wouldn't want any manual actions on top of that, as that'd equal another manual action to be done each time we perform reporting.

have you tried text to columns in the data tab along the top, got me over a few issues.

dave

 

[Leferty]

*bump*