9 Fantastic Functions for Cleaning Dirty Dates in Excel

[@marceljunioregondi5594]

John you just saved a whole lot of headache i have been facing when trying to have timelines in my dashboards. Thanks a bunch.

[@Up4Excel]

That's great and exactly what I hope my videos will do....reduce headache, save time, improve results. Thanks for commenting 👍John

[@johnborg5419]

Thanks John. Very interesting!!! :) :)

[@Up4Excel]

Glad you think so John, thanks for your support John 👍

[@shaileshsolanki957]

In your last example, what if there are mix of both date first and month first?

[@Up4Excel]

Not sure how you end up with that but unless there is some way of telling which is which (for example another column which indicates which it is) then any solution is prone to error....e.g. Is 1/4/25 1st April or 4th Jan???

[@Omar9557-oc6zf]

Can you provide some guidance on how to complete rearrange a date format in excel. For example, i have 203 cells with mixed date of birth formats. I.e.: (1)01/02/1995 (2)02/01/1995 (3) 1/2/1995 (4)1/02/1995 and so on. how do i go about correcting this?

[@Up4Excel]

You need some way of determining which is the day and which is the month for each line otherwise there is no way of knowing the difference between your example 1 and 2. Once you know that you can use the techniques in the video and a few IF statements. If you can't establish which part is the month then no function or technique could ever work. Hopefully you can sort it 👍 John

[@Unethical1991]

Hi, how to fix dates with dot in Excel when I have also normal, clean dates like that: 07.06.2023 14:41 and below for example: 2023-05-05 13:25:13.040? This is in the same column.

[@Up4Excel]

Use a column to find the doors and another column to find the - and then combine these 2 columns with IF(NOT(ISERROR to pick the one that gave an actual number. You might need to look a few things up there but should give you a start 👍 John

[@louisdupreez2753]

I have a problem with copied time ranges. How ccan I contact you Sir?

[@Up4Excel]

That sounds like a problem for Google not me 🤔

[@dilipbiswas459]

Great Sir....👍👍👍👍👍

[@Up4Excel]

Glad you like it 👍

[@dilipbiswas459]

Sir, this date problem please solve in google sheet

[@emmanuelchiamaka8164]

Very Educative

[@Up4Excel]

Happy to hear you got something from it 👍 John

[@ajinkyaredkar1993]

Amazing!

[@Up4Excel]

Glad you think so. Hope you get good use from the techniques 👍 John

[@ajinkyaredkar1993]

It did! It saved me my weekend!

[@Up4Excel]

@@ajinkyaredkar1993 Great Stuff

[@mohamedshalaby7688]

May I ask about good tips for date when back slash replaced with 0 and not consistent Here how it look like: 1101102016 = NOV 11 2016 601402014 = JUN 14 2014 402002017 = April 20 2017 100102016 = OCT 1 2016

[@Up4Excel]

Tricky as you don't have a consistent string length so can't just replace zeros in certain positions. Try a combination of the SUBSTITUTE, MID, and SEARCH functions to find zeros and replace them starting from the end of the text where you know you have a 4 digit year. John

[@tutsecret499]

Speaking of dates. Can you make a video using the correct formula to find accurate age based on Date of Birth column. Needs do be accurate the age to apply for retirement eligibility, to find out if the person is minor, to get benefits. Including feb 28, 29 the leap day? I know how to calculate age in Access database, but not in Excel.

[@Up4Excel]

You're in luck it's simply =TODAY()-A1... Where A1 is the cell with the date in. Make sure you format the answer cell as a number but a date though 👍 John

[@SanchoBruh]

Everything is okay but my problem is how i make a single column for the date where both the correct format and the cleaned format is stored

[@Up4Excel]

I'm not certain what you mean, but you can format the cleaned date any way you like once you have it in a column?