Wow!! So simple after watching this video. Same type of question was asked in an interview and unfortunately I couldn't answer that. Thanks I understood the concept now.!!
Very helpful video and thank you but i am trying to convert a date like this ( 28th January 2017 ) to a date format ( DD/MM/YYYY) is there anyway to do this
yes there is always a way! This formula assumes that the date is that format (28th January 2017 or 2nd May 2017) Here is the formula: =DATE(RIGHT(A1,4),MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)-5)&" 1")),LEFT(A1,FIND(" ",A1)-3)) It is a bit of a monster but I have tested it and it will work. Hope this helps.
thank you, I will try it and see. But i did find another way using " Find and Replace". Find the "st,nd,rd,th part of the date ad it automatically changed to the correct date format.
Best way would be to separate them using delimiter in text to column. Like u ca ude space on 1st occasion 21st will be separated and then u can use substitute. In fact u can use text to column but not with delimiter but with width option to break into 21 and st separately. Once u have days months year u can always firm a date with them with date formula.
hi i have this information as text 20200930 and my excersise says " you will need to separate and rejoin the separate parts of the date using an appropriate date function" . I tried with a conctenate(left,mid,right) but is not working . I dont know if I have to put value before al the formula o what am i missing,. Thanks
Assuming that the text is in A1 and the date format being used is YYYYMMDD then a formula you could use in another cell (not A1) would be =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Sir I have 2 dates with some text(For example "system maint - Nov'19-Oct'20) I want it to convert with date in 2 different cell I mean start date 1st for the month and end date last of the month. please advise
I would need some more information about the dates and the text before I can offer assistance. Could you tell me do they all start with "systemmaint - "? Do you want the dates to be the 1st day of the month? Do all the cells end with the format "mmm'yy-mmm'yy"?
Hello again Mark, You will need to use two formulas to split this up or you could do it all in one but for example sake I will give you two formulas, one for the date then one for the time. This assumes a "T" is always used as the separator. To extract the date =DATEVALUE(LEFT(A1,FIND("T",A1)-1)) and to extract the time =TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("T",A1))) Once they are divided you can format the cells for dates or times in excel. I have videos for both of these specific functions if you need more details. Hit the like button if you have not already thanks.
I've got a tricky one here: July 22nd 2019 and August 21st 2019 It needs to apply to different month lengths and dump the "nd" and "st", etc.....Seems like some of the examples might apply but cannot get it to work.
Hello Mark, Assuming your date formats are all structured the same way (as in your comments) this formula should work for any month year and day. The formula assumes the starting date is in cell A1 but you could change it if needed. =DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&" 1")),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-3))
@@marklouismichaelsen or u can use text to column use space as the delimiter once. Then process accordingly. It will save u from writing unnecssary left right substitute formula. Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
Hi, I need to convert 12/aug/18 to excel format, I tried playing around with the functions above but unable to construct the right formula, could you please help.
=DATE(RIGHT(A1,2)+100,MONTH(DATEVALUE(MID(A1,FIND("/",A1)+1,3)&" 1")),LEFT(A1,FIND("/",A1)-1)) This assumes your date is in cell A1. On a side note that format should be acceptable to excel. You should be able to highlight the cells and on the Home Tab choose Date for the number format. Let me know if this works.
Use text to column very easy from there. U can use date formula after separation if u want to play with separate no. Or u can simply select nothing ss delimiter and opt dmy format
I have a problem because when i put the numbers it gives me everything correct except year, for example i have 1/1/1876 and when i complete it gives me 1/1/3776. Please help :(
I believe excel date functions are only capable of handling dates from 01/01/1900 to 31/12/9999. Thanks for watching and sorry I couldn't be more help.
assuming that date is in A1 you could add a formula in B1 like this: ="01-"&A1 Adding the 01 should turn this into an acceptable date format and then you can format the date to whatever format you would like. I hope this helps.
Use delimiter as space in text to column to break it into 2 string. And then take the first part if the date and convert it into suitable date formats with no as the months and not like feb or Aug.
It was fantastic. You sounded like James Bond of Excel to me. It's nice to meet you. Thank you for your kind help!
Worked EXACTLY as described. Just what I needed except I had spaces instead of dots. Thanks a lot.
Sir, you saved a lot on me. Regards and respects from india
This gave me asmr shivers. so good. thank you.
Wow!! So simple after watching this video. Same type of question was asked in an interview and unfortunately I couldn't answer that. Thanks I understood the concept now.!!
deym. the most helpful video ive been looking for in extracting this activity im doing in excel lol
Still saving lives in 2021..true to God..lol
Absolutely fantastic stuff... very informative.
Bloody legend
saved my day! thanks :)
Fantastico....✌✌
Text to column tool would have done it quicker, selecting the dots to separate the columns, but this was really clever!
Very true and an excellent option for converting dates! Thanks for the feedback Roberto.
just great...enjoyed,,,
Thank you so much
Thank you! Your video was very helpful
Yieeey, thank you for this. It helped me a lot. 😘
Great Video
Wow, it helped me a lot
wow , thank you so much.
Thanks for the great video, it helped me a lot!
life daver, thank you
Excellent stuff! Subbed!
Very helpful video and thank you but i am trying to convert a date like this ( 28th January 2017 ) to a date format ( DD/MM/YYYY) is there anyway to do this
yes there is always a way! This formula assumes that the date is that format (28th January 2017 or 2nd May 2017) Here is the formula: =DATE(RIGHT(A1,4),MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)-5)&" 1")),LEFT(A1,FIND(" ",A1)-3)) It is a bit of a monster but I have tested it and it will work. Hope this helps.
Did my suggestion work for you?
thank you, I will try it and see. But i did find another way using " Find and Replace". Find the "st,nd,rd,th part of the date ad it automatically changed to the correct date format.
Hi, I need to convert 12/Aug/18 to excel date format, I've tried to play around with the functions above but unable to get the right formula.
Best way would be to separate them using delimiter in text to column. Like u ca ude space on 1st occasion 21st will be separated and then u can use substitute. In fact u can use text to column but not with delimiter but with width option to break into 21 and st separately. Once u have days months year u can always firm a date with them with date formula.
i want to convert "Fri, May 17, 17:00 PST"
into "17-05-2019 17:00" automatically. how can i do that?
But data which I have it's don't have dot between date and month and year
hi i have this information as text 20200930 and my excersise says " you will need to separate and rejoin the separate parts of the date using an appropriate date function" . I tried with a conctenate(left,mid,right) but is not working . I dont know if I have to put value before al the formula o what am i missing,. Thanks
Assuming that the text is in A1 and the date format being used is YYYYMMDD then a formula you could use in another cell (not A1) would be =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
@@sandorrethy thanks man !! It worked!!
Thanks a lot for the video. It is really helpful. I was wondering if you can help me convert text to time using a similar format. For example (9:32am)
would the data have brackets around the time? 24h format or 12h
Sandor Rethy, no there are without brackets. Like : 9:32am
=TIMEVALUE(LEFT(A1,LEN(A1)-2)&" "&RIGHT(A1,2))
this assumes that time value "9:32am" is in A1 Let me know if this helps
You are an excel wizard!! Thanks a lot.. it did work
Really glad I could help. Happy holidays!
Sir I have 2 dates with some text(For example "system maint - Nov'19-Oct'20) I want it to convert with date in 2 different cell I mean start date 1st for the month and end date last of the month. please advise
I would need some more information about the dates and the text before I can offer assistance. Could you tell me do they all start with "systemmaint - "? Do you want the dates to be the 1st day of the month? Do all the cells end with the format "mmm'yy-mmm'yy"?
Working with another string: 2019-08-12T21:56:37 The 'T' is problematic for me. Any ideas on how to convert that to a DATETIME?
Hello again Mark,
You will need to use two formulas to split this up or you could do it all in one but for example sake I will give you two formulas, one for the date then one for the time. This assumes a "T" is always used as the separator. To extract the date =DATEVALUE(LEFT(A1,FIND("T",A1)-1)) and to extract the time =TIMEVALUE(RIGHT(A1,LEN(A1)-FIND("T",A1)))
Once they are divided you can format the cells for dates or times in excel. I have videos for both of these specific functions if you need more details. Hit the like button if you have not already thanks.
@@sandorrethy Thank you very much Sandor. I was able to get this work for what I needed: =VALUE(SUBSTITUTE(A1,"T"," "))
I've got a tricky one here: July 22nd 2019 and August 21st 2019 It needs to apply to different month lengths and dump the "nd" and "st", etc.....Seems like some of the examples might apply but cannot get it to work.
Hello Mark, Assuming your date formats are all structured the same way (as in your comments) this formula should work for any month year and day. The formula assumes the starting date is in cell A1 but you could change it if needed.
=DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)-1)&" 1")),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-3))
@@sandorrethy That worked! Thank you SO MUCH
@@marklouismichaelsen or u can use text to column use space as the delimiter once. Then process accordingly. It will save u from writing unnecssary left right substitute formula.
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
Hi, I need to convert 12/aug/18 to excel format, I tried playing around with the functions above but unable to construct the right formula, could you please help.
=DATE(RIGHT(A1,2)+100,MONTH(DATEVALUE(MID(A1,FIND("/",A1)+1,3)&" 1")),LEFT(A1,FIND("/",A1)-1))
This assumes your date is in cell A1. On a side note that format should be acceptable to excel. You should be able to highlight the cells and on the Home Tab choose Date for the number format. Let me know if this works.
Use text to column very easy from there. U can use date formula after separation if u want to play with separate no. Or u can simply select nothing ss delimiter and opt dmy format
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
I have a problem because when i put the numbers it gives me everything correct except year, for example i have 1/1/1876 and when i complete it gives me 1/1/3776. Please help :(
I believe excel date functions are only capable of handling dates from 01/01/1900 to 31/12/9999. Thanks for watching and sorry I couldn't be more help.
I found other way crtl+h may repalce 5, apr replace 4 ect
Hy Sandor, i want to convert "Mar-18" this type of date (its in text format) in to correct date format 01/03/2018...please help
assuming that date is in A1 you could add a formula in B1 like this: ="01-"&A1
Adding the 01 should turn this into an acceptable date format and then you can format the date to whatever format you would like. I hope this helps.
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there..
9 Mar 2018
7 Jun 2018
25 May 2018
How to convert this to date format
=DATE(RIGHT(A1,4),MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,3)&" 1")),LEFT(A1,FIND(" ",A1)-1))
this assumes the first date is in A1.
i want to convert aug/12/2018 11:20:30 to 08/12/2018 11:20...please help
Use delimiter as space in text to column to break it into 2 string. And then take the first part if the date and convert it into suitable date formats with no as the months and not like feb or Aug.
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
Thanks for the great video, it helped me a lot!
Excellent! Thanks for watching.
Lasr3n