It was fantastic. You sounded like James Bond of Excel to me. It's nice to meet you. Thank you for your kind help!
@Deeps88
6 жыл бұрын
Fantastico....✌✌
@oddanneout
4 жыл бұрын
This gave me asmr shivers. so good. thank you.
@raghubelagam6881
3 жыл бұрын
Absolutely fantastic stuff... very informative.
@rudycramer225
Жыл бұрын
Worked EXACTLY as described. Just what I needed except I had spaces instead of dots. Thanks a lot.
@dannieldeleon7362
Жыл бұрын
deym. the most helpful video ive been looking for in extracting this activity im doing in excel lol
@kratosempire3984
4 жыл бұрын
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 :(
@sandorrethy
4 жыл бұрын
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.
@shafiqadil1989
5 жыл бұрын
i want to convert "Fri, May 17, 17:00 PST" into "17-05-2019 17:00" automatically. how can i do that?
@dundermoose
6 жыл бұрын
Excellent stuff! Subbed!
@jamespeterbrannan
Жыл бұрын
Bloody legend
@vinodsaw
4 жыл бұрын
But data which I have it's don't have dot between date and month and year
@shahmarbayramov1414
3 жыл бұрын
I found other way crtl+h may repalce 5, apr replace 4 ect
@vikramrajoria
2 жыл бұрын
Sir, you saved a lot on me. Regards and respects from india
@soarchla411
3 жыл бұрын
Still saving lives in 2021..true to God..lol
@malcstreet34
7 жыл бұрын
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
@sandorrethy
7 жыл бұрын
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.
@sandorrethy
7 жыл бұрын
Did my suggestion work for you?
@malcstreet34
7 жыл бұрын
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.
@mlucky256
6 жыл бұрын
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.
@SIMRANKAUR-eg7nc
3 жыл бұрын
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.
@jessamaejoyvarona3231
2 жыл бұрын
Yieeey, thank you for this. It helped me a lot. 😘
@rajusinghtezu
5 жыл бұрын
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.!!
@mauriciosalazar7418
2 жыл бұрын
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
@sandorrethy
2 жыл бұрын
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))
@mauriciosalazar7418
2 жыл бұрын
@@sandorrethy thanks man !! It worked!!
@goharkhatri6780
4 жыл бұрын
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
@sandorrethy
4 жыл бұрын
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"?
@markuy1542
4 жыл бұрын
i want to convert aug/12/2018 11:20:30 to 08/12/2018 11:20...please help
@SIMRANKAUR-eg7nc
3 жыл бұрын
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.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
@wagsbass
4 жыл бұрын
life daver, thank you
@debeshghosh5978
6 жыл бұрын
9 Mar 2018 7 Jun 2018 25 May 2018 How to convert this to date format
@sandorrethy
6 жыл бұрын
=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.
@Lyme62
7 жыл бұрын
Thank you! Your video was very helpful
@robertonavarro9913
5 жыл бұрын
Text to column tool would have done it quicker, selecting the dots to separate the columns, but this was really clever!
@sandorrethy
5 жыл бұрын
Very true and an excellent option for converting dates! Thanks for the feedback Roberto.
@marklouismichaelsen
5 жыл бұрын
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?
@sandorrethy
5 жыл бұрын
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.
@marklouismichaelsen
5 жыл бұрын
@@sandorrethy Thank you very much Sandor. I was able to get this work for what I needed: =VALUE(SUBSTITUTE(A1,"T"," "))
@akhiltm1
Жыл бұрын
saved my day! thanks :)
@UlfatSahil
4 жыл бұрын
just great...enjoyed,,,
@zothilemaake63
4 жыл бұрын
wow , thank you so much.
@mfmalth
6 жыл бұрын
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
@sandorrethy
6 жыл бұрын
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.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there..
@kartheekannallakalathi1845
3 жыл бұрын
Wow, it helped me a lot
@marklouismichaelsen
5 жыл бұрын
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.
@sandorrethy
5 жыл бұрын
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
5 жыл бұрын
@@sandorrethy That worked! Thank you SO MUCH
@SIMRANKAUR-eg7nc
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.
@sumayprabhu9044
2 жыл бұрын
Thank you so much
@vishvarajroadlines2020
6 жыл бұрын
Thanks for the great video, it helped me a lot!
@mlucky256
6 жыл бұрын
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.
@sandorrethy
6 жыл бұрын
=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.
@SIMRANKAUR-eg7nc
3 жыл бұрын
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
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". I accidentally came across it. Handling dates has been beautifully explained there.
@boydphakati667
4 жыл бұрын
Great Video
@lasr3n877
7 жыл бұрын
Thanks for the great video, it helped me a lot!
@sandorrethy
7 жыл бұрын
Excellent! Thanks for watching.
@suparnapaldas7954
6 жыл бұрын
Lasr3n
@TaralShah511
6 жыл бұрын
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)
@sandorrethy
6 жыл бұрын
would the data have brackets around the time? 24h format or 12h
@TaralShah511
6 жыл бұрын
Sandor Rethy, no there are without brackets. Like : 9:32am
@sandorrethy
6 жыл бұрын
=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
@TaralShah511
6 жыл бұрын
You are an excel wizard!! Thanks a lot.. it did work
Пікірлер: 68