The tutorial explains how to use Excel functions to convert text to date and how to turn text strings into dates in a non-formula way. You will also learn how to quickly change a number to date format. Continue reading
by Svetlana Cheusheva, updated on
The tutorial explains how to use Excel functions to convert text to date and how to turn text strings into dates in a non-formula way. You will also learn how to quickly change a number to date format. Continue reading
Comments page 15. Total comments: 731
hey i want to convert date into next date..
Example:- 1/4/2015= 2/4/2015
15/4/2015=16/4/2015
Hey,
as far as I can see, you just need to add one day.
This topic explains how to that :)
Very good solution. Converted the below format date into standard format by following the above steps. Thanks alot.
Feb 1, 2012 2:39:39 PM AST
8.6.2017
8.6.2017
dd.mm.year
calculate the days with current today date
Hello,
if I understand your task correctly, you will need to use DATE function. You will find the instructions on how to work with the function on this blog post.
If you need an instant solution, you can try our Date & Time Wizard from our collection of add-ins for Excel - Ultimate Suite. You can download its trial version from this web page.
Love, love, love your site. It helped me so much. So easy and fast finding help. I will recommend to everyone. Thank you
Hi,
Good Day!
How to convert complete no of entries in ss from 2017-07-03 12:47:47 IST to 2017-07-03 12:47:47, i need to remove text(IST) in all the date entries.
How to convert below date format into "DD/MM/YYYY HH24:MI:SS"
Jul 1, Sat 2017 6:30:01:236
Jul 1, Sat 2017 6:30:01:330
Jul 1, Sat 2017 6:30:01:421
Jul 1, Sat 2017 6:30:01:564
Jul 1, Sat 2017 6:30:01:678
Jul 1, Sat 2017 6:30:42:303
Please assist.
you're awesome
this is awesome. thank you!
please how do i convert 25/5/17 "twenty-fifth may twenty seventeen" to a format excel recognizes? thank you
Hi,
I have a bunch of date/time stamps that look like this:
1497541289
1497541291
1497541294
1497541297
1497541299
1497541300
How do I convert them to real dates & times?
Thanks,
Pete
Hi, Pete,
could you please specify what are these values stand for?
It's just the very last date in Excel (December 31, 9999) is stored under the 2958465 number. The stamps you provided simply can't represent the integers of the dates for Excel.
So, for us to help you better, please specify what these values should transform into exactly.
Thanks!
Hi,
I have also the DateStamp like : 1551363970 and I need to transform into : 2/28/2019 2:26:10 PM.
Is any formula to conver this?
Thank you,
Hi Emil,
Assuming your date stamp is in A2, please try this formula:
=A2/(24*60*60)+DATE(1970,1,1)
Don't forget to apply the needed date format to a cell with a formula.
hi team,
i am trying to convert excel date format from 5/29/2017 to 2017-05-29 but not working. can anyone give me a hand?
Thanks.
Please help me, If I have in a cell this: 2015-06, and I need to have Jun-2015, How should I do?
Thanks for the help, great job!
all failed
i had to retype dates
best way
Hi,
I am using Google sheet. I have a column in which the date is entered as text "Apr 21,2017 - Apr 25,2017". Now, I need your help to convert this to 04/21/2017 - 04/25/2017.
Please help me, if possible.
Thank you in advance.
Good morning!
my date data is in custom data type '00000000'
changing it to string loses the leading zero and using the formula
=DATE(RIGHT(E3,4), LEFT(E3,2), MID(E3,3,2))
also does not work with the leading zero. any thoughts?
example below from my worksheet
10021980 10/02/80 (great)
03301980 09/01/82 (not good)
I had a similar problem to Joshua's with leading zeroes. I was able to solve it using an "if" argument with the "len" argument to adjust for where the date formula picked up the month and day numbers from the string.
My data were, for example:
14.09.74 (in cell e2, format mm.dd.yy)
02.11.78
my formula was: =DATE(RIGHT(E2,2),MID(E2,IF(LEN(E2)=8,4,3),2),LEFT(E2,IF(LEN(E2)=8,2,1)))
I hope this helps anyone searching for help on this particular problem with leading zeroes.
Hello, Joshua,
could you send us your sample workbook with the data and the result you expect to get to support@ablebits.com? It's a bit difficult to help you right now, since we need to take a look at the way your data is formatted and arranged.
one way around is
=DATE(RIGHT(E3,4), LEFT(E3,len(e3)-6), MID(E3,3,len(e3-5)))
this way it can change the spot it starts reading the number from depending on the length of the number string since it doesn't see the lead 0
I am not sure if this website is still checked, but can I get help writing a formula for the following number format. 00102998, the first number would be the year, so 2000 in this case. It actually goes 1 – 0 for 1990 – 2000. Then the second two numbers are the month. The day is not important and can be any 2 digit number.
Leading 0 issue
my formula works for dates with no leading zero
=DATE(RIGHT(E2,4), LEFT(E2,2), MID(E2,3,2))
10021980 10/02/80 -> worked
03301980 09/01/82
Hi,
I have a column with 'Apr 7, 2016 10:39 AM' type data in it. How do I convert this text into a date/time format so I can use it to do further calculations? I.e Find the difference in time elapsed between 'Apr 7, 2016 10:39 AM and Apr 14, 2016 09:21 AM'?
I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?
Hi, Izzy,
activate the cell with a text you want to transform, then under the Home tab find Number format box, and choose Long Date or Short Date to your liking. If it already says Date – your data is ready for calculations.
Hi,
I know this is an old, thread, but I've tried everything so far, so I'm gonna give this a shot as well.
I have the same issue as Izzy and I've tried what you advised in your reply as well as the options in the article but as a I result I only get either the date "2/1/2020" or only the time "00:15:00". Regardless of formatting, I can't get them both as Date+ time or "2/1/2020 00:15:00". Any ideas?
Converting a number to date for me isn't working.
For example (when I follow your directions), I have a "number-date" of 1926. It "converts" to a date of 4/10/1909. 1927 converts to 4/11/1909, etc.
Hello Benny,
Please give an example of your full "number-date" and expected result. We will try to work out a proper method.
Hi ,
i'm trying to sort the below list of data in excel,
03/26 0:00
03/26 1:00
03/26 2:00
03/26 9:00
03/26 10:00
but the results are not as per my requirement
03/26 0:00
03/26 1:00
03/26 10:00
03/26 2:00
03/26 9:00
expected :
03/26 0:00
03/26 1:00
03/26 2:00
03/26 9:00
03/26 10:00
So i believe the issue will be resolved by converting the time to 00:00 format. i dont want to do it manually . could you help me out ?
Hi, Chitti,
it is most likely that Excel recognizes the data as a plain text, and you need to convert it. Please, follow the instructions from the article to check that in your document and convert the data.
how can i convert this date "25/3/2017" to "3/25/2017"
Follow the steps from this article to change date format or create your own. As easy as pie :)
Dear, I am very depressed to calculate the age in Excel so please help me to solve my problem mostly my work to calculate is for example
10.04.1987
04.08.1999
10.04.1987
04.08.1998
10.04.1986
04.06.1992
30.04.1981
02.08.1996
11.04.1989
02.07.1993
more than 4000 or 5500 date of birth have been calculated please share with me any formula to solve the matter in seconds please
Hi, Ali,
here you'll find a great tutorial and some variations on how to calculate the age quickly.
=+"Date- "&TODAY()
Value is -42797 How to convert In One Cell for Date ( Date-42797)
Date-03/02/2017
can you please help me in taking difference between creation time and the current time of the particular log data
Creation time 02/23/2017 12:29:34 PM
Current Time 02/25/2017 18:40:33 PM
Difference in hours
Can you please explain about the Date converted to General numbers.
example: 1/1/2000 while convert to general number 36526
How to calculate the number?
Hi,
I try to show a date in format MMMM YYYY - coming from DD-MM-YYYY. I use the below function to convert the date:
TEXT(B5;"[$-409]mmmm yyyy")
From date '01-12-2016' I would get 'December yyyy'.
Any idea why year is not shown, when month is?
I am using macros and i want to convert serial number 42779
to a date. I will not be able to ctrl+1 and choose from the menu. Is there any other option ?
Good day
i tried with no success.
9/26/2016 95
9/26/2016 95
blank 30551
i calculated the number of days from today to the 2016 date, however the blank cell indicate thousands days. How do i calculate it? please help.
i used the following formula.
=NETWORKDAYS(G3,TODAY(),IFERROR(G3,0))
how to convert exel colum 4,5,6,7,8,1,2,3, this serial 1, 2,3,4,5,6,7,8 plz send me formula
Respected
i want to to convert some date of birth of format 24/01/2017 into words of
format twenty for January twenty seventeen in excel
june 17 2012 it is a text string i have to convert it into date format like 17-jun-2012
please resolve my query.
Hello, how do i convert dates for example 2017-02 to 17-Dec? I need to enter a formula in the next column.
2017-02
2016-12
2016-12
2017-01
2017-01
2017-02
2017-02
2017-03
2017-01
2017-02
2016-12
ifound this helpfull
108462 748 3 11710 9 1
i want extract date as 03/11/17
so please help me
HI Svetlana Cheusheva,
Need to convert this 26/06/2005 to date format i.e.
twenty six June Two Thousand Five
please help..
how to convert date like 12-12-2006 to tweel december two thousend six pls help me and pls say is this possible or not in ms-excel
13/11/2016 16:44:32 this is text please convert to australian date and time
Thank you!
how can i convert a text date like this Nov/28/16 1:52 PM to a regular date cell like this 11/28/16?
Hello,
How can I change the date Eg 1116 to text format 1116?
I WRITE LONG NUMERIC VALUE IN EXCEL IT CONVERT INTO SCNTIFIC VALUE 1.1032E+28 WHEN I FORMAT CELL INTO TEXT IT DOESNOT GIVS ME CORRECT VALUE
help on quarterly date display e.g Jan-March.
thanks for your help, though am not able to find a formula that can display cell. please help.
Regards,
Jeremmy.
hello,
i got for example:
oct-19-2016
and want to convert it into:
19.10.2016
do u have a solution for this?
thanks anyway!
Can the below formatted data be converted to Date format:
Sep 1 2016 10:43AM
Sep 2 2016 10:53AM
Sep 20 2016 10:37AM
Sep 21 2016 10:54AM
Sep 22 2016 10:51AM
Please help me use date function to entire row
Can I apply date value formula to entire row. If yes please help
I have a data to be entered excel like 4-12 and 5-8 ...etc. But it is automatically converted in to date format. How to overcome it.
In my case I am having dates in different format as mentioned below. How can I convert those in to a single format.Please share your ideas.
Eg;
01-12-2015
12-30-2016
2016.30.12
12.30.2016
Help me to convert theses above date to common format like date-month-year.
bingo, you are great ablebits.