Can't do basic date calculations

My knowledge of using dates in excel must be pretty close to zero.

I've got a program that puts out dates in this format

2/20/2023

I want to be able todo a compare to see if these dates are before or after a particular day.

Formula:

Please Login or Register  to view this content.

I understand I need to format the cell type in a way that excel recognizes that its a date, and the calculation will work out.

From what I've read it should be simple....So i go into format cells and down to custom and create a custom type of "m/dd/yyyy". Which does not work.

If someone could explain how this works that would be great,

Thanks

Jon


ExampleDate.xlsx

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Excel will (should!) recognise 2/20/2023 as date with whatever format is used for the cell. See attached (UK format dates)

Your cells contain text strings and not date serial numbers. Changing number format does not change anything about the underlying cell value, so they remain text.

Common solutions for converting "numbers/dates stored as text" into real numbers: https://support.microsoft.com/en-us/...rs=en-us&ad=us

If it helps, since my system is set to recognize MDY dates, I had not trouble using the Text to Columns command to convert the dates as text to real dates. Your file indicates that the text is being imported from the output of another program. You might look at the process you use to import these values and see if you can get Excel to correctly recognize these dates as part of that import process.

As you have 365, you can get the proper date value in the column headed "I removed the time" by using this formula.

Please Login or Register  to view this content.