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.
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
ReplyDeleteCheers,
Andy
MSFT Office Outreach Team
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...
ReplyDelete=--TEXT(C28,"0000-00-00")
Rick Rothstein (MVP - Excel)
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")).
ReplyDeleteHowever the use of -- to convert text to value is new learning for me.
Thanks once again for valuable tip.
what is C28,iam not able to understand. Please help.
Deletewhat is C28,iam not able to understand. Please help.
Delete@mona dogra,
DeleteFirst, a teaching moment.... When you have a question about the syntax of a excel function, select a blank cell then click the fx button toward the side of the Formula Bar... this will bring up a dialog box with a list in its center... scroll the list until you find the function whose syntax you want to check and click it, then click the "Help on this function" link in the bottom left corner of the dialog box an you will get all the help available for that function.
Okay, now to answer your question, C28 is a cell reference... it is the assumed cell where the 8-digit number representing a date was entered.
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...
ReplyDelete=1*TEXT(C28,"0000-00-00")
=0+TEXT(C28,"0000-00-00")
=TEXT(C28,"0000-00-00")/1
and so on.
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=exact(A1,B2)
Deleteresult will show True / False.
Hello msardar
ReplyDeleteWelcome 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
Need help please. How will you convert this string?
ReplyDeleteFeb 2 2010
Hello Yani
ReplyDeleteUse 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.
Thank you - worked perfectly for me.
DeleteThis comment has been removed by the author.
DeleteTo achive the full versality, don't use DATEVALUE, as it is as locale-oriented as the input itself. It just depends on the locale settings choosen in Control Panel. Better use
Delete=DATE(MID(A1,7,4), VLOOKUP(MID(A1,1,3), MthLkpRng, 2, FALSE), MID(A1,4,2))
instead, when MthLkpRng is the 12 by 2 range containing Jan, Feb, etc. in its first column and 1, 2, etc. i the second one.
This way your workbook will be working under all kinds of national versions, independently on the format set, months' names etc. -- important when intercooperating.
Tomek
hi i am using =DATE(RIGHT(A6,4), LEFT(A6,2), MID(A6,4,2)) from text to date but if i put month 12 to 13 the result add 1 month extra
Deletehow to prevent
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...
ReplyDelete=--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)
need help how can I convert "Forecast as of February 08, 2010" to "2/08/2010" thanks.
ReplyDeleteThis comment has been removed by the author.
DeleteHello Sukru
ReplyDeleteYou 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
awesome you are genius
ReplyDeleteThank for your help
ReplyDeleteI 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 ?
Hi hectoro
ReplyDeleteYou 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
This comment has been removed by the author.
ReplyDeleteHello Sir,
ReplyDeleteToday, 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
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
ReplyDeleteHi Brian
ReplyDeleteIf 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
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!'?
ReplyDeleteHi Brian
ReplyDeleteYes - 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
That's why DATEVALUE should be NEVER (almost) used. The only way to have ever-working solution is with DATE function.
DeleteTomek
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:
ReplyDelete=--((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.
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!
ReplyDeleteThis 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@ac1983,
ReplyDeleteI'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.
Hi,
ReplyDeleteI 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
hi Arji,
DeleteYou have nothing to do too much. all u have to do is only change your cell format in Date Category. hope it will be helpful.
76400 is shown as 4 Mar,2109.
Regards
Amarjeet Sharma
Hi Arji
ReplyDeleteI have no idea about sql it will be better if you try some sql forum for help on this.
Regards//Yogesh Gupta
Yoresh,
ReplyDeleteThank 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?
@G.
ReplyDeleteGive this formula a try...
=--SUBSTITUTE(A1," ",", ",2)
The easiest way to convert a text value (which basically represents a date) is just to use it in formula like
ReplyDelete= A1+0
This works wonderfully!!!!
Thank you. So simple :)
DeleteYogesh,
ReplyDeleteI 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?
Wonderful information you got here. Thanks for taking the time to provide such valuable tips. This will help literally thousands of people.
ReplyDeleteRegards
Daniel
Hi Yogesh,
ReplyDeleteThank 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
@VollGaz,
ReplyDeleteYou 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.
@Rick,
ReplyDeleteThanks 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
@VollGaz,
ReplyDeleteI 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.
@Rick
ReplyDeleteSorry 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
I have text count of 9/23
ReplyDeletehow to covert this in to date
@Nilesh,
ReplyDeleteNot 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
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.
ReplyDeleteI have date in mm/dd/yy format like 10/30/2010.
ReplyDeletebut 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
@jbsoft,
ReplyDeleteExcel 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).
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.
ReplyDeleteWhen 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?
I want to change a date from 10012011 to 10/01/2011, the formulae above do not work.
ReplyDeleteI'm using excel 2007.
Hi Chris,
Deleteuse this one, hope your query must be solved........
=DATEVALUE(MID(A3,1,2)&"/"&MID(A3,3,2)&"/"&MID(A3,5,4))
Where A3 means cell having your Date value(10012011)
That is not a good example to use in your question... we cannot tell if that is supposed to be October 1st or January 10th... you should always use a day value larger than 12 so it is obvious how your dates are formatted.
DeleteHi Yogesh,
ReplyDeleteGreat 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?
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.
ReplyDeleteAny ideas?
@Steve,
ReplyDeleteAssuming 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.
Hoping you can help me with converting text to time in Excel.
ReplyDeleteFor 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
Thanks - i have taken a different approach that works.
ReplyDeleteRick - Brilliant!
ReplyDeleteWorks Great.....
hi yogesh can i change in date figure to word in excel
ReplyDeletepl help me
@DILIP,
ReplyDeleteHere 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
Hi Yogesh
ReplyDeleteI want split / convert belod date and time in seprate colum in excel.
Apr 23 2011 1:36PM
Please help.....
Regards,
Jignesh Rajput
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.
ReplyDeletePlease help.
Thanks,
Greg
Hi Greg,
DeleteFollow these step and hope your query will be solved as well although it's a bit long....
1. select the cell
2. click on text-to-column button located on Data Tools sub menu on ribbon in Data (menu)
3. click next
4. check on Others and type / (slash) in text field given next to it.
5. click next
6. click on finish
after this you will see your date has been divided into three parts following 5,10 and 2011 in next cells.
use this formulla
=CONCATENATE((A1,B1,C1) Where A1,B1 and C1 are those cells having 5,10 and 2011
hope your query has been resolved.
Use the TEXT function to duplicate what formatting is doing to the "julian" date...
ReplyDelete=TEXT(A1,"mmddyy")
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
ReplyDeleteHi Yogesh,
ReplyDeleteCongrats 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?
how would i convert?
ReplyDeleteFriday, August 07, 2009 1:15 PM
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.
ReplyDeleteJOSE VYPANA
Perfect! Thank you for a good description! I even managed to sort it out with my swedish excel version :)
ReplyDeleteHow do i convert this from text to date format?
ReplyDeleteTue Jul 12 19:30:58 2011
I need to have it formatted as dd/mm/yyyy hh:mm:ss
I want to change a date from 10/01/2011,to tenth january two thausand eleven give me the formulae
ReplyDeleteHi,
ReplyDeleteI'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?
Hi,
ReplyDeleteI 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
@Ivan,
ReplyDeleteGive this formula a try...
=MID(A1&", "&A1,FIND(".",A1)+1,LEN(A1)+1)
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.
ReplyDeleteDisplay 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
BTW: I know why it's doing what it's doing, and I'll bet money I'm missing something simple.
ReplyDeleteTo convert this...
ReplyDelete2011-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.
Thanks a million for your simple and effective solution!
ReplyDeleteYou saved lots of my time!!!
Nico.
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.
ReplyDeleteThanks
Fazlul
use following formula
ReplyDelete=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
Hi Yogesh,
ReplyDeleteI 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?
This is giving me fits:
ReplyDelete2011-12-01 00:16:00 -05:00
@Medic,
ReplyDeleteI 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.
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?
ReplyDeleteSelect the column or the CELL you want to change, replace the . with a / and change the date format what you want !!!
ReplyDeleteHi Yogesh,
ReplyDeleteI 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....
Can any one please help me !!!!
DeleteI 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
ReplyDeleteHi Yogesh, thank you so much for the blog post - you have made my day a lot easier!
ReplyDeleteI 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
@adigitalmarketingblog
ReplyDeleteAssuming 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.
Sanjay:
ReplyDelete=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)
I have a data in more row with asa\asa\er\er\etr.pdf
ReplyDeletei want to brack from last slac\..
can u help me??
Moradhwaj Gupta-9376926928
Hi Yogesh,
ReplyDeleteI am entering 1-3 in a column and excel is converting it automatically it to 3rd Jan. I do not want that.. I want it as it is ie 1-3. Please suggest
Manoj
Hi Yogesh,
ReplyDeleteI have a 12 digits number and the 1st 6 digit is the birth date such as 540620015957 and the birthdate is 20/06/54. I wish that you could tell me how to extract just the birth date from this kind of numbers
Ken
Hi Yogesh,
ReplyDeleteI have a 12 digits number and the 1st 6 digit is the birth date such as 540620015957 and the birthdate is 20/06/54. I wish that you could tell me how to extract just the birth date from this kind of numbers
Ken
Hi Ken
ReplyDeleteYou can use following formula
=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))
This assumes your data is in cell A1, you will need to modify your formula accordingly
Hi Yogesh. I have a PDF file that shows a person's age as "76YR6M12D". I'm trying to convert it to a date file. I've read through the ideas here but nothing seems to work. I realize the conversion must be on the date the PDF was created (or the user must know the date the text string was created) or the Excel date might be converted incorrectly. Any ideas how I can get this text string to a date string in Excel? Many thanks!
ReplyDelete//Bill
as a follow up to my previous note... i have this formula... =DATE(YEAR(TODAY())-MID(E13,SEARCH("YR",E13)-2,2),MONTH(TODAY())-MID(E13,SEARCH("M",E13)-1,1),DAY(TODAY())-MID(E13,SEARCH("D",E13)-2,2))
ReplyDeleteIt returns the correct month and year but not the correct day. Any ideas?
Hi all.......i'm having an issue with that....i have to change 160513080212 into 16:05:13 08/02/2012
ReplyDeletethanking you in advance for your kind help
I have to find diffe between two date. Some dates are DD-MM-YY format and some are mm-dd-yy format. How can i bring in to same format.
ReplyDeleteHello All...
ReplyDeleteCan someone suggest me how to concatenate two cells which has two different data types... for ex: A1 cell has text "Anil" and B1 cell has date 1/3/2012 (this date is coming from different sheet using the formula =--Right([sheet2]!$D$2, 12)
I want the result in C1 cell as "Anil 1/3/2012"
Regards,
Anil
hi there. amazing forum with so much help. good work guys.
ReplyDeletei have a list of dates that have been inported into excel but they appear as 25/4/2,007 no matter how i try to format the cell i can not get it to change to a date or be recognised as a date.
i have tried to remove the , seperator in the control panel but this has not helped.
any ideas how i can convert his into a useable date?
many thanks in anticipation.
allistair
hi there. amazing forum with so much help. good work guys.
ReplyDeletei have a list of dates that have been inported into excel but they appear as 25/4/2,007 no matter how i try to format the cell i can not get it to change to a date or be recognised as a date.
i have tried to remove the , seperator in the control panel but this has not helped.
any ideas how i can convert his into a useable date?
many thanks in anticipation.
allistair
Hello Yogesh
ReplyDeleteI am trying to format a western data format which was exported into text to a date format. I have the following 25.03.12 I want to convert it into date format to 25/03/12 can you help?!
YOGESH,
Deleteactually rectifying, I need the 25.03.12 to convert to US date format: 03/25/12 (MM/DD/YY).
Can you or anyone else help me please?! Cheers!!!
juss replace . with /
Delete=>Select 25.03.12 in A1 Cell
Delete=>Data=> Text to Column for "."
then
=B1&"/"&A1&"/"&C1
Thank you!!!! Worked.
DeleteHi,
ReplyDeleteI have an issue with date where the date is in the pattern 1947 03 01 in column however we would like it so that it is in 01 03 1947 or 01 March 1947.
I've tried to MID it out with strings, but that doesnt seem to work, do any of you have any kind suggestions?
Thanks!
After some playing about and the Excel Help - I found how to do this e.g. If its Column I then:
ReplyDelete=RIGHT(I8,3)&"/"&MID(I8,6,2)&"/"&LEFT(I8,4)
i have date with DOB like DD MM YYYY. how to convert it to DD/MM/YYYY.
ReplyDeleteIf it is a string and you have a reference cell in A1:
Delete=LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,4)
This comment has been removed by the author.
DeleteI need to convert 2011-12 to Dec-2011 in Excel 2010
ReplyDeleteIf your date is in A1
Delete=DATEVALUE(RIGHT(A1,2)&"/"&LEFT(A1,4))
Then just change the format of your cell to mmm-yyyy under custom settings
This comment has been removed by the author.
ReplyDeleteI have a csv file that includes case numbers formated like yy-cccc,
ReplyDeleteyear-casenumber, but when I open it in Excel 2010, it automaticially converts it all to a date; for example 11-7141 is displayed as 11/1/7141 or Nov-41. How can I format these columns to display the case number
11-7141 as 11-7141, or is there a way to prevent Excel from converting data when it opens the csv file? Thank you in advance for any help on this.
Thanks for the information - this was a very helpful formula during crunchtime.
ReplyDeletecan someone help me convert 20120708001300 to 07/08/2012 00:13:00
ReplyDelete@Angela Asamoah,
ReplyDeleteIf the 07 is meant to be the month and the 08 the day, then you can use this formula...
=1*TEXT(A1,"0000-00-00 00\:00\:00")
But you will have to use Cell Format to format the cell as the date/time display that you want.
Thanks .. It was really helpful. :)
ReplyDeleteThnx a lot Rick, it worked perfectly
ReplyDeletehow to convert text like 01.01.2012 into date format
ReplyDeleteHow to convert mm/dd/yyyy to dd/mm/yyyy. I tried iwth format cells. but I have to send this to usa client who want it in dd/mm/yyyy format.
ReplyDeleteI have date time in one cell. How to separate them into two cells as date and time
ReplyDeleteThanks
Hi there. I have a text format of 24/05/2011 01:00 and would like to maintain the format (e.g. xx/xx/xxxx xx:xx) but convert to an Exel date so I can group using pivot tables. I looked through previous posts but can't make any of these work. Suggestions appreciated.
ReplyDeleteFound the solution: I have forced Excel to regard it as text by placing a single quote in front of each value. Column B contains the date/time value of whatever is in column A using the formula:
Delete=--TEXT(A2,"dd/mm/yyyy hh:mm")
@eplain,
DeleteYou should be able to just do this...
=--A2
and then format the cell with the date/time format of your choosing.
Hi all
ReplyDeleteGreat forum here.
I have a list of unsorted numbers in a column and I want to find the missing numbers between 0 and 3000. Is there a way this can be achieved, macro or formulae. You will be saving me a great deal of work load.
BR
Hi,
ReplyDeleteplease tell me how to convert the text "Sep 18,2012" to "09-18-2012".
hi all -- can any body guide me how to recognise whether date has been filled in a cell in excel.something like ISBLANK returns true if cell is blank . Say something like ISDATE which returns true if date has been filled in any format( i use dd mmmm yy )
ReplyDeletei want to convert 27/09/2012 into 27.09.2012 how do i?
ReplyDelete@shahid majeed . I think you can use find and replace for the same press ctrl+f -->go to replace tab-->in find box type / and in replace type . this will work but if you have . in other cells also it will also get replaced
Deleteyou can use find (/) and replace all(.)your problem will resolve.
Delete@Shahid Majeed - assuming 27/09/2012 is in A1, then =SUBSTITUTE(A1,"/",".") should work.
ReplyDeleteThanks everyone for all the interesting tips on this page!
Ben
hi
ReplyDeletei want to convert 23-Oct-12 to Tue 12/10/23??
any help would be appreciated
Nov 15, 2012 how do i convert this into 11/15/12?
ReplyDeletethanks
for excel 2010
ReplyDeleteThanks Yogesh your tips are realy helpfull
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteDear sir, How to sort a long list of date of birth starting with day, month and year example existing DOB: 31.12.12
ReplyDeleteYour urgent reply is highly appreciated.
Jess
You totes saved my bacon - thanks!!!
ReplyDeleteWhat a fantastic site! I thought, for sure, I'd find how to convert an entry like "5/1/2012 5:09:29 PM" into an Excel date value, but I did not. Any suggestions? (I need to preserve both the date and the time, so this example would convert to 41030.71492)
ReplyDeletePl let me know how can i get the accounting type display format through programming in C#.Net.
ReplyDeleteI using your Yogesh.ExcelXml so pl let me know according this asap.
Thanks
Anil
i need to convert excel text format to Date & Time format.
ReplyDelete2013-JAN-01 04:14pm to 1-Jan-2013 04:14 pm
Thanks
Sujee
I need to convert 5 digit text, 10913 (= MDDYY) to a date: 1/09/13. Please help.
ReplyDelete@Sandy Winkelman,
ReplyDeleteGive this formula a try...
=1*Text(A1,"0-00-00")
It will produce a 5-digit date serial number, so just format the cell with the date format of your choice.
can someone help me with a function or formula in crystal reports? i have fields like Msisdn, Create date, Closed date,Group and assigned date.
ReplyDeletei want a report that gives only unique MSISDN values, i dont want duplication of the msisdn in case more that one appears. how do i limit that field
my source file for the create date gives me a serial numbers with 10-digit length (e.g. 1298358542, 1298363966, 1298624411) and i am having a hard time converting them as DATE. can someone please help me?
ReplyDeletethank you!
I have a string stored as 2/13/2013 12:00:00 AM. can anyone help in getting this in date format?
ReplyDeleteHello,
ReplyDeleteI found very usefull tips about Excel. Here I am using Excel 2007. I am trying to calculate total no. of months between two dates. I want to use edate() function but not found in my list and flash error. How can I add this one?
Please help me about.
Thanks and regards,
Yogesh Kulkarni.
Very great information!
ReplyDeleteIs there a way to convert a text of 32013 (meaning March 20, 2013) to 03/20/13? Once the month goes to October the text would have a different number of digits (i,e, 101513 - October 15, 2013).
Thank you for any assistance.
Brett
@Rick Rothstein (MVP - Excel) - your solution ;
Delete=1*Text(A1,"0-00-00")
worked for what I was looking for. Better yet, after doing a little playing with what I needed - while importing the data from a TXT file, and going through the delimited characters import wizard, I was actually able to format the particular cell to the correct result.
Thank you for your assistance.
Yogesh ji, Namaste...I want to convert Date in to Text Format i.e. 17/08/1977 as Seventeen August Neneteen Seventy Seven... Plz help with thanx
ReplyDeleteHow do you convert March 23 1966 to 03/23/1966? (there is only one space between the fields. no commas)
ReplyDeleteHow do you convert March 23 1966 to 03/23/1966? (there is only one space between the fields. no commas)
ReplyDeleteThis comment has been removed by the author.
ReplyDelete@Manish Tiwari,
ReplyDeleteYou should be able to use the UDF (user defined function) that I posted in my mini-blog article here to do that...
http://www.excelfox.com/forum/f22/spell-date-out-words-314/
@Ron Cipres,
ReplyDeleteI think you can just use Excel's Text-To-Columns feature to do that. Select your column of dates, press ALT+de to bring up the Text-To-Columns dialog box, select Delimited and press Next, make sure the Space checkbox is NOT checked and press Next, click the Date option button in top right corner and select MDY from its dropdown, then click the Finish button. This should convert your text to real dates which you can now use Cell Formatting on to make it display any way you want.
I need to convert 03/15/1964 to 15/03/1964 in Excel 2007. 03 is month and 15 is date
ReplyDeleteYou are a world wide Excel Guru !!!!, thanks for the great tips!!!!!
ReplyDeleteHi Yogesh,
ReplyDeleteI am facing one difficulty in Excel.
I need to convert this format (Tue Dec 22 00:00:00 IST 2009) to YYYY-MM-DD in Excel.
Could you please help me on this...
Thanks in Advance.
@Ritesh,
ReplyDeleteTry this formula...
=1*REPLACE(TRIM(MID(A1,5,LEN(A1))),7,13,",")
@Ritesh,
ReplyDelete{Follow up} I forgot to mention... my formula returns the serial date number for the date, you will need to Custom Format the cell using...
yyyy-mm-dd
for the Type pattern.
I am still having difficulty converting a date/time format to serial number in Excel.
ReplyDelete17/07/2013 12:14 (with one space between the date and time) to a format like 40159.20145
I have data from a .csv file that I import into excel. It turns my date/time into excel date format - BUT when I change the date (in the new sheet) it is impossible to convert the date/time back to an Excel serial number that can be used for excel calculations - hence requiring the solution above.
Any ideas and EXAMPLES please would be greatly appreciated.
Solved: In my own response to my question above I found the solution.
ReplyDeleteIn Windows 7 I had changed my Regional Settings enabling me to show the full day date month year below the time in the notification area.
I went back to Control Panel > Regional and Language Options > Customise at Regional Options Tab > Date Tab
make sure the SHORT DATE format is set as follows:
Short Date Format : mm/dd/yyyy
Date separatotor : /
Click OK after you have done setting as above.
Restarted my excel file and the formula started working.
I could edit the date (as per my previous question) and not have it convert to text!
I had previously tried:
=A2+0 (if the date was in A1)
Ctrl+Shift for the date (by highlighting the date cell first)
Ctrl+Shift; for the time (by highlighting the time cell first)
=TEXT(A1,"dd/mm/yy") - in cell A2
=DATEVALUE(A1)
=TIMEVALUE(A1)
=DATEVALUE(A1)+TIMEVALUE(A1)
=--A1
=DATEVALUE(MID(A1,4,2)&"/"&MID(A1,1,3)&"/"&MID(A1,7,4))
All returned =#VALUE - so my message is simple please check the basic formatting first, it will save you a lot of anguish, head scratching and frustration!
Need help
ReplyDeleteMy company writes Purchase Order Numbers where 07253 is the first five digits before a store code and represents July 25, 2013.
I need a formula that will convert the date and - ideally - tell me if today is more than 30 days after the purchase order was written
@Robin,
DeleteMaybe this formula (assuming D2 is the cell where the first Purchase Order Number is located)...
=IF(DATE(2010+RIGHT(D2),LEFT(D2,2),MID(D2,3,2))-TODAY()>30,"More than 30 days","Less than or equal to 30 days")
wow. that's awesome, thanks
DeleteHi!! Yogesh!!
ReplyDeleteI have dates stored in text value eg. 26/07/2013. I used datevalue, fdate functions, resulted in error.
Could you suggest a better formula to convert ?
Ramki
thank you for information. has been stuck for 2 days, my problem is when imported date from another file, type of date is different so my formula cant detected.
ReplyDeletei can fix this problem using this formula
1*text(A1;"dd/mm/yyyy")
A1 is date, i put for all formula before using if then.
http://besteparfums.com
Hello Yogesh
ReplyDeleteNeed a help...
I have date 'Aug 19 2013 7:14AM' which I am trying very hard to get into this format 'mm/dd/yyyy hh:mm'.
Could any one help me on...Please...
@Shibu John,
ReplyDeleteYou do not actually have a date... you have text which to you LOOKS like a date (Excel only sees text). You can get an actual date value from this using this formula...
=INT(REPLACE(REPLACE(A1,FIND(" ",A1,FIND(" ",A1)+1),0,","),LEN(A1),0," "))
This will return a date serial number, so you will have to use Cell Formatting to make it look the way you actually want. If your intent was to have the value in the cell be an actual date (rather than have a formula that refers to it), then you will have to enter your text in propert date format... you would need a comma after the day number and a space separating the AM or PM from the number preceding it; for example...
Aug 19, 2013 7:14 AM
Thank you Rick..! for your help...
DeleteIt was very helpful.. This particular date format was tool generated and I do have huge data with this data format. It will manual task which is hectic in correcting them..
I was able get the date as required -'Aug 12 2013 8:16AM' the outcome was -> '08/12/2013 00:00'
Is there any possibility to get even the time as well with formula.
You can use this instead...
Delete=1*REPLACE(REPLACE(A1,FIND(" ",A1,FIND(" ",A1)+1),0,","),LEN(A1),0," ")
Note: You will have to format the cell using a date format of...
mm/dd/yyyy hh:mm
Hello Rick,
DeleteI have an format of April 14, 2015, 12:00:00 AM, Which I need to change dd/mm/yyyy hh:mm
I was trying to use the formula mentioned above it was not working please let me with this.
Ohh..it works great..!!!
ReplyDeleteThank you Rick....for making life easier..
I need to combine the following formulae in one formula
ReplyDelete1) A1 = dd/mm/yyyy converted to text = TEXT(A1, "yymmdd")
2) A1 = mm/yyyy convert to text = TEXT(A1, "yymm")&00
Either of the arugements in A1 is needed to be converted as TEXT format
combining above formulae
Thanks
@tmk,
DeleteWhat is in A1... a "real" date? If so, you will not be able to do what you are asking for, at least not dynamically. This formula would work as long as A1 already had a formatted value in it...
=IF(CELL("format",A1)="D3",TEXT(A1,"yymm\0\0"),TEXT(A1,"yymmdd"))
BUT, if change the format for A1, nothing will happen until you recalculate the worksheet.
Rick, its great, thanks for your prompt solution..
DeleteAn unformatted / 'General' cell filled with a date eg., 9/9/2013 or 9/2013 automatically gets formated as 09/09/2013 (Date - UK Format) or Sep-13 (custom format; reformatting - mm/yy shows 09/13). Your formula yeilds desired result 130909 or 130900 ('00' for no day in the 2nd instance); [these values are considered as Expiry dates in Barcoding].
Is there a way to format a given cell to show either dd/mm/yy or mm/yy for dynamic calculations.
Hello, Great to see so many people getting good help here.
ReplyDeleteI am trying to convert a string time into an Excel time.
=TIMEVALUE("7:02 AM")
evaluates to #VALUE!
This is not how TIMEVALUE is supposed to work!
What am I doing wrong?
It's OK.
ReplyDeleteThis works
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6,"AM",""),"PM",""),"12","0"))+IF(RIGHT(B6,2)="PM",0.5,0)
for any of these:
7:02 AM
7:30 PM
12:45 AM
12:30 PM
Cheers.
I have a number 201312 and i need to convert it to YYYY format.
ReplyDeleteusing the excel formula converts it to 1905
any help?
hi yogesh
ReplyDeletei need to date to convert words in hindi formate
As 01-12-2014 to ,d fnlEcj nks gtkj pkSng
sharma
Cool...thanks! if you guys really wanna text your way through the right one or just any woman you want then it's time to talk what girls like and check out this page RIGHT NOW! -->www.talkgirlslike.com/. Discover the tricks, techniques, the science, art, and the magic of texting her into your arms now. Totally worked on me. I highly recommend this! Cheers :)!
ReplyDeleteHi Yogesh,
ReplyDeleteI want to transform 25.04.2014 in 04/25/2014.Can I do this?
Hi Can any help me with below conversion
ReplyDeleteA1 - 7h 54m 2s
Output - 07:54:02
Hi Yogesh,
ReplyDeleteWe are working on a project wherein we read data from Excel and export it to data, in the data we have a Date Column, now in SQL we read this in dd/MMM/yyyy format, now i want the users to enter date in that column but it has to be automatically formatted as dd mmm yyyy format. can you kindly help us on this.
Hi Yogesh,
ReplyDeleteInput where I have is this format ( April 14, 2015, 12:00:00 AM) Where I need change to normal format . Please let me know how to do it
Dear
ReplyDeleteI had a problem where I am trying to write 12-11 in cell, which need to be kept in the same form. But excel converts it into date form automatically. Is there any way to keep it same form.
Dear Yogesh sir sql database show date 1387170169 in this number how to convert date format in excel please suggest
ReplyDeleteWrite query
ReplyDeleteConsider date as ’21-Jun-2014’ and remove ‘-‘from date and then display middle character from available string.
thanks for the tips and information..i really appreciate it.. convert pdf to excel
ReplyDeleteHi Yogesh,
ReplyDeleteI will get reverse Julian dates (20140501) along with partial dates (201405). Now i want to convert into date format like 01May2014 and 00May2014. Can you help me, how to get it.
Regards
Raghu
This comment has been removed by the author.
ReplyDelete