John - very good. I have a list of dates with events which i wish to filter by type of event. However, some of the dates are dates, some text and some two dates (so also text) - as we have not decided on the date the event will take place. I would like to display the filtered list with the date - i guess it will have to be in text format to accommodate the two date version (eg 8th or 9th January). Pivot tables need consistent format in a column. What to do?
I think you're correct they'll need to be treated as text. You can turn the true dates into text with the =TEXT function to keep everything consistent. 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
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
@@latifahfahad6121 I think you just need to watch it again. All the formulas show up at the top of the screen and you can look them up online if you don't fully understand them. John
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
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
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
Came in really handy. Thanks John
Great to hear, that's what it's all about 👍 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
brilliant !!
Glad you think so. Thanks for saying. John 👍
Thanks John. Very interesting!!! :) :)
Glad you think so John, thanks for your support John 👍
John - very good. I have a list of dates with events which i wish to filter by type of event. However, some of the dates are dates, some text and some two dates (so also text) - as we have not decided on the date the event will take place. I would like to display the filtered list with the date - i guess it will have to be in text format to accommodate the two date version (eg 8th or 9th January). Pivot tables need consistent format in a column. What to do?
I think you're correct they'll need to be treated as text. You can turn the true dates into text with the =TEXT function to keep everything consistent. 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
Amazing!
Glad you think so. Hope you get good use from the techniques 👍 John
It did! It saved me my weekend!
@@ajinkyaredkar1993 Great Stuff
Very Educative
Happy to hear you got something from it 👍 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???
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
Thank you, but I didn’t understand the Trim part 😢
TRIM removes all spaces from the beginning and end of the text in a cell. Is that what you meant?
I mean the last part from the video, it wasn't clear for me how you cleaned the dates there using the different formelas 8:43 @@Up4Excel
@@latifahfahad6121 I think you just need to watch it again. All the formulas show up at the top of the screen and you can look them up online if you don't fully understand them. John
@@Up4Excel i am just not sure what is the concept of combining the formulas
I have a problem with copied time ranges. How ccan I contact you Sir?
That sounds like a problem for Google not me 🤔
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?
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
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
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