Date Format Issues / Conversion

[Cat129]

Hi Everyone,

Can anyone help me with date conversions please,

I receive a file from our timesheet program with everyone's hours in, and the date format is Canadian (mm/dd/yyyy), I am in the process of putting something together to process this data. The issue I have is that depending on what country you are in the date formats are different, so for me my excel is English (dd/mm/yyyy).

When I use formulas within date ranges there are errors because of the difference, for example 5th June 2020, in my original data is it shown as 06/05/2020, but when I use formulas to capture information between dates, it is read as 6th May 2020.

How do I get around this?

Thanks,

 

[etaf]

how do you know which is Canadian and which is english - I assume some will come in as text - like 12/24/21

I receive a file from our timesheet program

If the file is exported from the timesheet program - is it exported as Excel or some other format - and then imported into excel, is it a CSV file or other format , if exported from the program, can you ask for to be export as CSV file ?
If so - rather than just open in Excel and let excel decide on the date format
Use the IMPORT part , then you specify a column DATE format to be MM/DD/YYYY