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???
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?
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
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.
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
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.
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
John you just saved a whole lot of headache i have been facing when trying to have timelines in my dashboards. Thanks a bunch.
That's great and exactly what I hope my videos will do....reduce headache, save time, improve results. Thanks for commenting 👍John
In your last example, what if there are mix of both date first and month first?
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???
Amazing!
Glad you think so. Hope you get good use from the techniques 👍 John
It did! It saved me my weekend!
@@ajinkyaredkar1993 Great Stuff
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?
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
Very Educative
Happy to hear you got something from it 👍 John
Thanks John. Very interesting!!! :) :)
Glad you think so John, thanks for your support John 👍
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.
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
Great Sir....👍👍👍👍👍
Glad you like it 👍
Sir, this date problem please solve in google sheet
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.
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
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
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?