Monday, October 12, 2009

Excel Convert Text to Date

Many of us get into a situation when the dates in our data are coded as text. Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula.

You may have a situation where the text of the date is actually as date but being a text string it is not recognised as date by excel. It could be 12/Oct/2009 in text string. You can easily convert them using Datevalue formula. Assuming you have text in cell A2, the formula you need to enter is =DATEVALUE(A2)

However many time we get data stored like 20091012. Here October 12, 2009 has been stored as First 4 digits as Year, next 2 Digits as Month, next 2 Digits as Day. It becomes little tricky but it can also be converted to date using combination of Mid and Date function of excel.

Assuming you have date stored as 20091012 in cell A3 of you worksheet, you can convert it to date using =DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

Logic behind this formula is devide the text into Year , Month and Day separatly. This is done by using MID function of excel. Once you are able to break text string into three different components of Date. You put these in the Date Function of Excel. Syntex for Excel Date function is DATE(year, month, day).

MID(A3,1,4) = 2009 - This Year Value in Text String
MID(A3,5,2) = 10 - This is Month Value in Text String
MID(A3,7,2) = 12 - This is Day Value in Text String

This is converted into Grand Formula = DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

If you see the formula I have just brokenup the text string using MID in combination with Date function.

See picture below to understand it better.


convert text to date in excel , excel convert text to date, convert text to date, date values

Posted by Yogesh Gupta at 3:57 PM
Labels: ,
Reactions: 

82 comments:

  1. Great post Yogesh! I've definitely run into this problem before and it is great to see the solution. Have you checked out the Excel community on Facebook? If not you should join the conversation at http://www.facebook.com/office

    Cheers,
    Andy
    MSFT Office Outreach Team
    ReplyDelete
  2. Another way to handle a "date" like 20091012 where the order for the numbers is a 4-digit year followed by a 2-digit month followed by a 2-digit day is this...

    =--TEXT(C28,"0000-00-00")

    Rick Rothstein (MVP - Excel)
    ReplyDelete
  3. Hello Rick - Let me first welcome you to my blog. Then thanks for valuable tip. I never thought it like this. Even If I had thought it like this , I would have used =DATEVALUE(TEXT(C28,"0000-00-00")).

    However the use of -- to convert text to value is new learning for me.

    Thanks once again for valuable tip.
    ReplyDelete
  4. Excel is very accommodating in that if you use a text value that is a number (dates are numbers... days offset from January 1, 1900) in a calculation, then it will convert the text into a number so that it can perform the calculation. On top of that, if the number can be interpretted as a date value, then Excel will go ahead and to that as well. The yyyy-mm-dd date format is a universally accepted format for an unambiguous date, so TEXT function was set up to output that format. However, the output from the TEXT function is, well, text. The double unary (the minus, minus) just means multiply the value by minus one twice. Minus one times minus one evaluates to 1, so putting the double unary in front of the text that can be interpretted as a number force the text to be involved in a calculation, so the text date is converted to a real date (the offset value from January 1, 1900) and multiplied by 1, which doesn't change its value, so the text date became a real date. Just so you know, the use of the double unary is not the only way this formula could have been constructed... any thing that involves the text in a calculation that doesn't change its value would have worked. For example, these formulas all do the same thing as my originally posted formula...

    =1*TEXT(C28,"0000-00-00")
    =0+TEXT(C28,"0000-00-00")
    =TEXT(C28,"0000-00-00")/1
    and so on.
    ReplyDelete
  5. I have got many useful tips from this page. kindly let me know how to compair two column in excel sheet (i m using office 2007)
    ReplyDelete
  6. Hello msardar

    Welcome to my blog

    Your question is not very clear. If you just want to compare two cells then you can use following formula

    A1=B1

    This will return true/false result based on contents in both the cells.

    Regards//Yogesh Gupta
    ReplyDelete
  7. Need help please. How will you convert this string?

    Feb 2 2010
    ReplyDelete
  8. Hello Yani

    Use following formula to convert string Feb 2 2010

    =DATEVALUE(MID(A1,4,2)&"/"&MID(A1,1,3)&"/"&MID(A1,7,4))

    This considers that you have date string in cell A1 , so change the reference accordingly.

    In this we are converting date string into 02/Feb/2010 using mid and join text option. then using datevalue function to convert into excel date.

    Let me know in case you need any further help with this.
    ReplyDelete
  9. While it may look strange, here is formula that can be used to convert a text string "date" of the form mmm d yyyy or mmm dd yyyy into a real Excel date...

    =--SUBSTITUTE(MID(A1,5,11)," ",LEFT(A1,3))

    This version will convert the same format either with or without a comma after the day number...

    =--SUBSTITUTE(MID(SUBSTITUTE(A1,",",""),5,11)," ",LEFT(A1,3))

    Rick Rothstein (MVP - Excel)
    ReplyDelete
  10. need help how can I convert "Forecast as of February 08, 2010" to "2/08/2010" thanks.
    ReplyDelete
  11. Hello Sukru

    You can use following formula considering you have your value in cell A1. Then format cell as m/dd/yyyy

    =--RIGHT(A1,17)

    Now understand that how it works. With Right(A1,17) we are extracting sting February 08, 2010 which is 17 charactors long. by putting -- before this we are converting it to excel date. Now you can format it in whatever manner you like.

    The trick is to extract the date from text and then convert it to excel date.

    Thanks
    Yogesh Gupta
    ReplyDelete
  13. Thank for your help
    I use the =datevalue(a1) formula but an error shows
    #value
    my text that was copied to a1 says 01/24/2010
    Can you tell me why ?
    ReplyDelete
  14. Hi hectoro

    You need to check your windows date settings. It should match the text date format. Change the date setting as below.

    Control Panel > Regional and Language Options > Customise at Regional Options Tab > Date Tab

    Short Date Format : mm/dd/yyyy
    Date separatotor : /

    Click ok after you have done setting as above.

    Restart your excel file, you will see that your formula will start working

    Thanks
    Yogesh Gupta
    ReplyDelete
  15. This comment has been removed by the author.
    ReplyDelete
  16. Hello Sir,
    Today, I was searching for a Excel formula that converts digits into words. I did "google" it and found your blog link where i found many useful tips for excel in respond to my search. Nice and diligent work.
    Thnx
    Ali Khan, Pakistan
    ReplyDelete
  17. Hi - the above information has been very helpful. However, I have a slightly different problem. I'm trying to import Google Analytics data into Excel and want the dates in date format rather than text. A text example is 'Thursday, February 25, 2010'. I have managed to split up the day (i.e. '25'), month and year using MID, SEARCH, RIGHT and LEFT functions, but when I put them all together with your solution, I get #VALUE! as the result. Any help greatly appreciated. Thanks
    ReplyDelete
  18. Hi Brian

    If you are using date formula then all the inputs should be numbers. You are getting error as "February" is not a number.

    Instead I suggest you use following formula considering you have your text in cell A1

    =--MID(A1,FIND(",",A1)+2,LEN(A1))


    MID(A1,FIND(",",A1)+2,LEN(A1)) will remove day text such as Thursday from the text and you will get date formated as February 25, 2010

    -- in front of it will convert it in to excel date that can be formated as you wish them to.

    Regards
    Yogesh Gupta
    ReplyDelete
  19. Thank you for a great response. I understand the forumla and have used it but the '--' in front still causes me a '#VALUE!' result. If I exclude the '--' I get the date formatted as you suggest, but when I sort the date column it is in alphabetical not numerical order (i.e. it sees it as text). My PC is set up for UK date format, thus 'dd mmmm yyyy' as the long date - is this why I get '#VALUE!'?
    ReplyDelete
  20. Hi Brian

    Yes - you are correct the error is due to Long date setting of your PC. If you can change it to MMMM DD, YYYY the error will get resolved. You can revert back to original settings after doing paste value for the formula results.

    Else you will need a formula to format text date as per date settings on your machine which is a very lenghty formula.

    Let me know in case you need further help.

    Regards
    Yogesh Gupta
    ReplyDelete
  21. You have been so helpful - I now understand the problem and have written that very lengthy formula to convert a Google Analytics US date format into UK date format! So, for the date 'Wednesday, February 10, 2010', where the text date string is in field A2, the following code resolves the text into a UK date format as 10/02/2010:

    =--((MID((MID(A2,FIND(",",A2)+2,LEN(A2))),(FIND(" ",(MID(A2,FIND(",",A2)+2,LEN(A2)))))+1,(FIND(",",(MID(A2,FIND(",",A2)+2,LEN(A2)))))-(FIND(" ",(MID(A2,FIND(",",A2)+2,LEN(A2)))))-1))&" "&(LEFT((MID(A2,FIND(",",A2)+2,LEN(A2))),(FIND(" ",(MID(A2,FIND(",",A2)+2,LEN(A2)))))-1))&" "&(RIGHT((MID(A2,FIND(",",A2)+2,LEN(A2))),4)))

    Maybe there is a shorter code, but this works and I am very appreciative for your kind help. Incidentally, I had never come across your advice to use '--' before a date format and haven't seen reference to this in my Excel book - thank you again.
    ReplyDelete
  22. Thank you so much for posting this solution! Worked like a charm!! This is the first explanation of how to do this, that was easy to understand!
    ReplyDelete
  23. This site has some great tips. Although I have been able to put them into practice, I have recently come across values in text that need to be converted into a date. How would someone recommend going about changing "27 Jan, 2010" into a date without multiple steps?
    ReplyDelete
  24. @ac1983,

    I'm assuming those quote marks are not really in your cell's text. If that is the case, then simply select all the cells with dates that look like this, click Edit/Replace on Excel's menu bar, type a comma (nothing else) in the "Find what" field, leave the "Replace with" field empty and click the "Replace All" button... all the selected cells should become real dates which you can then format anyway you want.
    ReplyDelete
  25. Hi,

    I have different problem with Dates, when importing excel sheet into sql, dates have taken as nvarchar datatype and stored as ' 76,400 in rows.

    Could you please tell me how to convert this again to date
    ReplyDelete
  26. Hi Arji

    I have no idea about sql it will be better if you try some sql forum for help on this.

    Regards//Yogesh Gupta
    ReplyDelete
  27. Yoresh,

    Thank you for the earlier formula
    =DATEVALUE(MID(A1,4,2)&"/"&MID(A1,1,3)&"/"&MID(A1,7,4))
    to convert Feb 2 2010. Is there a way to adjust the formula to accommodate both single and double digit days, FEB 2 2010 and FEB 22 2010?
    ReplyDelete
  28. @G.

    Give this formula a try...

    =--SUBSTITUTE(A1," ",", ",2)
    ReplyDelete
  29. The easiest way to convert a text value (which basically represents a date) is just to use it in formula like

    = A1+0

    This works wonderfully!!!!
    ReplyDelete
  30. Yogesh,

    I had the following value in cell A2: 27\09\10

    I used: =DATE(MID(A3.7.2.),MID(A3.4.2),MID(A3,1,2))

    Excel somehow throws out a value of: 27 September 1910.

    Why does it say 1910? Can you please help?
    ReplyDelete
  31. Wonderful information you got here. Thanks for taking the time to provide such valuable tips. This will help literally thousands of people.

    Regards
    Daniel
    ReplyDelete
  32. Hi Yogesh,

    Thank you for a most useful blog!
    Is there a similar way (function or other) to convert a text expression representing a currency value/number into a real (recognized by Excel) currency value, like:
    =CURRENCYVALUE("2,800.00") ?
    I need to insert a text value into a cell via VBA and have it formatted as currency with two decimals..
    I hope you or someone else can help me out.

    Thank again for a great blog!

    Cheers Folke
    ReplyDelete
  33. @VollGaz,

    You would first set the cell's NumberFormat property to "$0.00" and then assign your value to the cell. For example...

    ActiveCell.NumberFormat = "$0.00"
    ActiveCell.Value = 123.456

    Of course, you can use normal cell or range reference in place of the ActiveCell reference.

    Note: I used $ for the currency symbol because I am located in the US. Unfortunately, I have no experience with non-US regional settings so I am not sure if you would still use the $ symbol for your locale's currency symbol (assuming it is not already the $ sign) or if you would use your locale's actual currency symbol in place of the $ sign I used... you will have to experiment with that.
    ReplyDelete
  34. @Rick,

    Thanks a lot for your answer. My problem with that approach is the fact that I don't have a handle to the cell directly. I only have the option to pass "something" to an in-between-C#-module - preferably a text expression like =DATEVALUE("") or your =--TEXT(C28,"0000-00-00") which Excel in turn interprets as a currency value.. unless I will have to change the C# module to be more flexible.
    If there's some way to make Excel see a text expression as a currency value - just by passing a single statement to the a cell, I am still very interested in hearing about it.
    But I have the feeling that I will have to expand the C# code...

    Cheers Folke
    ReplyDelete
  35. @VollGaz,

    I am strictly a VBA programmer, so I can't address how things will work using C# to enter values into Excel, but you can impose a Currency format when entering a value (either manually or using VBA) by prefixing the currency symbol onto the number. For example, from VBA...

    Range("A1").Value = "$123.45"

    This seems to default to two decimal places automatically IF your number has decimal values; HOWEVER, if that number is a whole number, then it defaults to 0 decimal places, but you can force the two decimal places by ending your whole number with ".00". In VBA, we would control this with the Format function...

    Range("A1") = Format(123, "$0.00")

    so if you have an equivalent to that in C#, then you can try using it. By the way, again, in VBA, you can convert a non-currency value to a currency value by prefixing the cell's value with a $ sign and then reassigning it back to the cell. For example...

    Range("A1").Value = "$" & Range("A1").Value

    Hopefully some of these ideas will translate over to your C# module that you are using to control Excel with.
    ReplyDelete
  36. @Rick

    Sorry for being away for a few days.
    It is so kind of you taking your time to answer so detailed :D I'm sure I can use your directions to my benefit. Very useful, thank you!

    Cheers Folke
    ReplyDelete
  37. I have text count of 9/23
    how to covert this in to date
    ReplyDelete
  38. @Nilesh,

    Not sure what you mean by "text count", but you can convert 9/23 to a date by concatenating a slash followed by the year and then prefixing the entire combination with a mathematical operator that does not change the underlying value. Assuming A1 contains your 9/23 value, try this formula...

    =1*(A1&"/"&YEAR(NOW()))

    which will probably return a 5-digit number that you can then Cell Format to whatever date format you want
    ReplyDelete
  39. I have extract the day of the week from the database, so when I paste the data in excel "Tuesday" becomes a text. Now when sorting in excel this "DayofWEek" column is handled like text sorting so Friday comes before Monday & so on. I need this sorted like a date so we have sun-sat sorting.
    ReplyDelete
  40. I have date in mm/dd/yy format like 10/30/2010.
    but when i put this formula:
    =left(a2,2)
    it gets 40 ???
    this formula works fine when cell contains only text.

    any idea about that?

    www.availbest.com
    ReplyDelete
  41. @jbsoft,

    Excel stores dates as a double... the interger part represents the number of days offset from 12/31/1899 for worksheets (VBA offsets from 12/30/1899) and the decimal part is the time represented as a fraction of a 24-hour day. So, you cannot do string functions on a date. However, there are date functions you can use. To get the month for a given date, use the MONTH function. In your case, you would use this...

    =MONTH(A2)

    Just so you know, there is also a YEAR and DAY function as well. As for the 'time', there are the HOUR, MINUTE and SECOND functions. In addition, you can use the TEXT function to get individual or combinations of these. For example, you can get the month using the also...

    =TEXT(A2,"m")

    One of the strengths of using the TEXT function is the variety of date parts it can return. For example, to get the month name instead of the month number, you could do this...

    =TEXT(A2,"mmm")

    for the abbreviated name and this...

    =TEXT(A2,"mmmm")

    for the full name. On thing more to note about the values returned by the TEXT function... they are always text values (the functions I mentioned at the begining of this response all return real numbers).
    ReplyDelete
  42. cannot get Excel to recognise 4 digits by themselves as a year. I am writing a list of events, with the date in a column (so we can check for milestone anniversaries). For some we have a full date (no problem), others just month and year (no problem) but others just the year itself.
    When I type in the year by itself - e.g. 1971 -and then do a calculation based on it - it assumes it is the 1971st day and converts it to (year) 1905. If I format the cell as yyyy, Excel immediately converts 1971 to 1905 when first entered. What I want is to be able to enter 1971, for Excel to display 1971 but for it to be stored as a date such as 01-01-1971 so I can use the YEAR() function to extract the year later on. Any ideas?
    ReplyDelete
  43. I want to change a date from 10012011 to 10/01/2011, the formulae above do not work.

    I'm using excel 2007.
    ReplyDelete
  44. Hi Yogesh,

    Great blog, I'm a sole trader attempting to formalise my books, I copy online bank statements but the currency is displayed as text. Selecting formatting in the column option does not seem to work, is there a simple solution that does not disturb the surrounding data?
    ReplyDelete
  45. I have a datetime problem. I need to convert this 03/10/2011  3:48PM into a datetime. I need this to calculate start end cycle times.

    Any ideas?
    ReplyDelete
  46. @Steve,

    Assuming you have that "date/time" in a cell and it looks exactly like you are showing us, then I think the easiest way to convert it into a true date/time is with a macro. Press ALT+F11 to open the VB editor, click Insert/Module on its Menu Bar, and then copy paste this code into the code window that opened up...

    Sub ConvertToDateTimes()
    Dim Cell As Range, S As String
    For Each Cell In Selection
    S = Replace(Cell.Value, " ", " ")
    If Right(S, 2) Like "[AaPp][mM]" Then
    S = Left(S, Len(S) - 2) & " " & Right(S, 2)
    End If
    If IsDate(S) Then Cell.Value = CDate(S)
    Next
    End Sub

    That's it, now go back to the worksheet and select the cells with your "date/time" values in it, press ALT+F8, select ConvertToDateTimes from the list and click the Run button. If your "date/time" values were consistly as shown, then they should now be true Excel date/time value which you can format to be displayed anyway that you want.
    ReplyDelete
  47. Hoping you can help me with converting text to time in Excel.

    For reasons I won't go into here, I am using VBScript to do some work on an Excel file. I need to take a column of text of the form 1013 (4 digits) and turn this into an excel time (10:13) so I can then subtract another time from it (e.g., 10:13 - 10:00 to get 13 minutes).

    I have tried a number of techniques, I am missing something...

    In the VBScript that follows, I get an error re FSTIME, but not ANALYSISTIME. Can you help me understand what I am missing?

    '-- format the FSTIME column as TIME
    Set objRng = objExcel.Range("CH:CH")
    objRng.NumberFormat = "h:mm"
    objWBlims.Save()

    '-- format ANALYSISTIME column as DATE/TIME
    Set objRng = objExcel.Range("BU:BU")
    objRng.NumberFormat = "m/d/y h:mm"
    objWBlims.Save()

    curRow=2
    Do
    ANALYSISTIME = FormatDateTime(objWSlims.Cells(curRow, 73).Value, 4)
    newFSTIME = objWSlims.Cells(curRow, 86).Value


    objWSlims.Cells(curRow, 82).Value = CallTimeSeconds(ANALYSISTIME, newFSTIME)

    curRow = curRow + 1

    Loop Until curRow = lastRow

    Function CallTimeSeconds(StartTime,EndTime)
    StartHour = Hour(StartTime)
    StartMin = Minute(StartTime)
    StartSec = Second(StartTime)
    EndHour = Hour(EndTime)
    EndMin = Minute(EndTime)
    EndSec = Second(EndTime)
    StartingSeconds = (StartSec + (StartMin * 60) + ((StartHour * 60)*60))
    EndingSeconds = (EndSec + (EndMin * 60) + ((EndHour * 60)*60))
    CallTimeSeconds = EndingSeconds - StartingSeconds
    End Function
    ReplyDelete
  48. Thanks - i have taken a different approach that works.
    ReplyDelete
  49. Rick - Brilliant!

    Works Great.....
    ReplyDelete
  50. hi yogesh can i change in date figure to word in excel
    pl help me
    ReplyDelete
  51. @DILIP,

    Here is a function that I have posted online before which you might be able to use or modify as needed...

    Function DateToWords(ByVal DateIn As Variant) As String
    Dim Yrs As String
    Dim Hundreds As String
    Dim Decades As String
    Dim Tens As Variant
    Dim Ordinal As Variant
    Dim Cardinal As Variant
    Ordinal = Array("First", "Second", "Third", _
    "Fourth", "Fifth", "Sixth", _
    "Seventh", "Eighth", "Nineth", _
    "Tenth", "Eleventh", "Twelfth", _
    "Thirteenth", "Fourteenth", _
    "Fifteenth", "Sixteenth", _
    "Seventeenth", "Eighteenth", _
    "Nineteenth", "Twentieth", _
    "Twenty-first", "Twenty-second", _
    "Twenty-third", "Twenty-fourth", _
    "Twenty-fifth", "Twenty-sixth", _
    "Twenty-seventh", "Twenty-eighth", _
    "Twenty-nineth", "Thirtieth", _
    "Thirty-first")
    Cardinal = Array("", "One", "Two", "Three", "Four", _
    "Five", "Six", "Seven", "Eight", "Nine", _
    "Ten", "Eleven", "Twelve", "Thirteen", _
    "Fourteen", "Fifteen", "Sixteen", _
    "Seventeen", "Eighteen", "Nineteen")
    Tens = Array("Twenty", "Thirty", "Forty", "Fifty", _
    "Sixty", "Seventy", "Eighty", "Ninety")
    DateIn = CDate(DateIn)
    Yrs = CStr(Year(DateIn))
    Decades = Mid$(Yrs, 3)
    If CInt(Decades) < 20 Then
    Decades = Cardinal(CInt(Decades))
    Else
    Decades = Tens(CInt(Left$(Decades, 1)) - 2) & "-" & _
    Cardinal(CInt(Right$(Decades, 1)))
    End If
    Hundreds = Mid$(Yrs, 2, 1)
    If CInt(Hundreds) Then
    Hundreds = Cardinal(CInt(Hundreds)) & " Hundred "
    Else
    Hundreds = ""
    End If
    DateToWords = Ordinal(Day(DateIn) - 1) & _
    Format$(DateIn, " mmmm ") & _
    Cardinal(CInt(Left$(Yrs, 1))) & _
    " Thousand " & Hundreds & Decades
    End Function
    ReplyDelete
  52. Hi Yogesh

    I want split / convert belod date and time in seprate colum in excel.

    Apr 23 2011 1:36PM

    Please help.....

    Regards,

    Jignesh Rajput
    ReplyDelete
  53. I need to convert a date to six digit text format to use in a string. eg. 5/10/2011 to 051011. I have formatted to achieve this with ddmmyy, but when I try to use this string or add to it always converts to julian date.
    Please help.

    Thanks,
    Greg
    ReplyDelete
  54. Use the TEXT function to duplicate what formatting is doing to the "julian" date...

    =TEXT(A1,"mmddyy")
    ReplyDelete
  55. I want to know the process to change the digit in words in MS excell. Please demostrate it. My name is Dheer Singh and my email Id is singhdheer08@gmail.com
    ReplyDelete
  56. Hi Yogesh,

    Congrats on maintaining an excellent blog which helps excel beginers like us.

    I am looking for a way to convert a date in one column in the format DD-MMM-YYYY to YYYY.MMM

    Is there any way to achieve the same?
    ReplyDelete
  57. how would i convert?

    Friday, August 07, 2009 1:15 PM
    ReplyDelete
  58. I have entered the date in the format dd/mm/yyyy format. I wanted this date format to be changed into text entries in which the entered data should be shown as ddmmyyyy with a green coloured sign on the top left hand corner, e.g. if i enter the date 25/12/2010 it should be displayed as 25122010 in the field with a green mark at the top left hand side corner. Is there any way to change the entered dates to be changed to the desired format. pls help.
    JOSE VYPANA
    ReplyDelete
  59. Perfect! Thank you for a good description! I even managed to sort it out with my swedish excel version :)
    ReplyDelete
  60. How do i convert this from text to date format?

    Tue Jul 12 19:30:58 2011

    I need to have it formatted as dd/mm/yyyy hh:mm:ss
    ReplyDelete
  61. I want to change a date from 10/01/2011,to tenth january two thausand eleven give me the formulae
    ReplyDelete
  62. Hi,

    I've got alot of data that l want to covert from two digits years to four digits.

    Current data format is: 85-86 , and l want to covert it to 1985-1986. All the data years are 1900's. Can any one help?
    ReplyDelete
  63. Hi,

    I have alot of data that l wish rearrange a text name.

    Current format is: A.Alan and l wish to covert it to Alan, A

    can anyone help, thanks
    ReplyDelete
  64. @Ivan,

    Give this formula a try...

    =MID(A1&", "&A1,FIND(".",A1)+1,LEN(A1)+1)
    ReplyDelete
  65. I have a general text "2011-10-31 22:19:09 GMT" and I need to convert to date AND time. I've tried several approaches and I'm not doing something right. I need to accomplish several things.
    Display in Date and time. I can get the date, but my time changes the date to 12/31/2003.
    This is my latest attempt:
    =DATE(MID(C4,1,4),MID(C4,6,2),MID(C4,9,2))*TIME(MID(C4,12,2),MID(C4,15,2),MID(C4,18,2)).
    Change yyyy/mm/dd to dd/mm/yy. Once I get the first hurdle I think I can get the rest of these.
    Convert timezone from GMT to Australia time zone. This will be simple by adding 19 hours, but in the mean time
    ReplyDelete
  66. BTW: I know why it's doing what it's doing, and I'll bet money I'm missing something simple.
    ReplyDelete
  67. To convert this...

    2011-10-31 22:19:09 GMT
    .
    to a real date, use this formula...
    .
    =1*SUBSTITUTE(A1," GMT","")
    .
    You will have to format the cell do display the date/time serial number the way you want.
    ReplyDelete
  68. Thanks a million for your simple and effective solution!
    You saved lots of my time!!!
    Nico.
    ReplyDelete
  69. Hello Sir, How are you? Hope you are good. I am ok. My birthday is 25/01/1985 and I want in excel to find what was my last birthday. How to do that please let me know.

    Thanks

    Fazlul
    ReplyDelete
  70. use following formula

    =IF(TODAY()>DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1)))

    This assumes that date of birth is mentioned at Cell A1, you will need to change the reference in your formula accordingly
    ReplyDelete
  71. Hi Yogesh,

    I need one help.

    I am importing data from excel into SQL database. while importing, the format of the numbers are changing and the numbers are not coming with separators.
    Can you five me some solution for this?
    ReplyDelete
  72. This is giving me fits:

    2011-12-01 00:16:00 -05:00
    ReplyDelete
  73. @Medic,

    I am almost positive this will yield the correct time...

    =LEFT(A1,LEN(A1)-8)-MID(A1,LEN(A1)-5,99)

    but if it does not, then change the minus sign in front of the MID function to a plus sign and try again.
    ReplyDelete
  74. I have a problem where the date is shown in one field as 12.18.2010 01:17:38 and it is a general format, when i want to transform it to a date format it doesn't work, can anyone help me with it?
    ReplyDelete
  75. Select the column or the CELL you want to change, replace the . with a / and change the date format what you want !!!
    ReplyDelete
  76. Hi Yogesh,

    I am new to the forum, I need a help in Excel. I have the login log out timings for my employees, I was able to get the No of Hrs worked for the people working in the day shift, by running a pivot with the minimum time and maximum time for the employee. However, I am stuck to calculate the No of Hrs worked for the people working in the night shift as the day changes. and the max time for that employee is not exactly the Log off time. Please help me !!!

    I hope I was clear please let me know if I was not clear....
    ReplyDelete
    Replies
    1. Can any one please help me !!!!
      Delete
  77. I have 23/11/2011 03:56 PM in one cell I want to extract date (dd/mm/yyyy) and time (hh:mm) into two separate cells in excel? Can you help me? Regards, Sanjay
    ReplyDelete
  78. Hi Yogesh, thank you so much for the blog post - you have made my day a lot easier!
    I do have a slight issue though, I'm wondering if you could help me? I've imported dates from analytics into excel, but they've come up as '201011' (2011, nov) etc. I was able to use your formula once I added '01' to the end and just treated all the figures I had as though they represented one day in a month. This is ok, but in this spreadsheet I have almost 3000 entries and to add '01' to the end of each is going to take a while! Anyone have any tips?
    Jen
    ReplyDelete
  79. @adigitalmarketingblog

    Assuming your "dates" are in Column A starting on Row 1, put this formula...

    =1*(A1&"01")

    on Row 1 of an unused column and copy it down to the last row containing one of your dates. Then select that column, copy it, select A1 (my assumed location for your first date) and use Paste Special's Values option to overwrite your 6-digit "dates" with the new 8-digit "dates". Delete the column you put the formula in and then proceed as in this blog article.
    ReplyDelete
  80. Sanjay:
    =DATE(TEXT(RIGHT(C2,4),"0000"),SUBSTITUTE(LEFT(MID(C2,FIND("/",C2)+1,LEN(C2)),FIND("/",MID(C2,FIND("/",C2)+1,LEN(C2)))-1),"-"," "),LEFT(C2,FIND("/",C2)-1)
    ReplyDelete
  81. I have a data in more row with asa\asa\er\er\etr.pdf
    i want to brack from last slac\..
    can u help me??

    Moradhwaj Gupta-9376926928
    ReplyDelete

Subscribe to: Post Comments (Atom)