In the previous article, we discussed different ways to convert text to date in Excel. If you are looking for a solution to the opposite task - changing an Excel date to text - a few choices are available to you again.

Traditionally, we'll begin with a formula solution and then explore a couple of non-formula ways.

The Excel TEXT function is specially designed to convert a numeric value to a text string and display it in the format you specify.

The syntax of the Excel TEXT function is as follows:

TEXT(value, format_text)

Where:

`value`

is a numeric value you want to convert to text. This can be a number, a formula that returns a numeric value, or a reference to a cell containing a number.`format_text`

this is how you want to format the resulting text value, provided as a text string enclosed in quotation marks.

For example, you can use the following formula to convert a date in cell A1 to a text string in the traditional US date format (month/day/year):

`=TEXT(A1,"mm/dd/yyyy")`

As you see in the screenshot above, the value returned by the TEXT formula is aligned to the left, which is the first sign that points to a date formatted as text. Apart from alignment in a cell, there are a few more indicators that can help you distinguish between dates and text strings in Excel.

Since Excel dates are serial numbers in their nature, the Excel TEXT function has no problem with converting them to text values. The most challenging part is probably specifying the proper display formatting for the text dates.

Microsoft Excel understands the following **date codes**.

**Months**:

- m - month number without a leading zero.
- mm - month number with a leading zero.
- mmm - short form of the month name, for example
- mmmm - long form of the month name, for example
- mmmmm - month as the first letter, for example
*M*(stands for March and May)

**Days:**

- d - days number without a leading zero.
- dd - day number with a leading zero.
- ddd - abbreviated day of the week, for example
- dddd - full name of the day of the week, for example

**Years:**

- yy - two-digit year.
- Yyyy - four-digit year.

To display the converted text date exactly the way you want, you can separate the date codes with various delimiters such as dash (-), slash (/), comma (,) colon (:), etc. Here are a few examples:

- "mm/dd/yyyy" - the date format used in the USA, displays as
*03/08/2015*. - "dd/mm/yyyy" - the date format used by the rest of the world, displays as
*08/03/2015*. - "dd-mmm-yy" - displays as
*08-Mar-2015*to avoid any confusion : ) - "dddd, mmmm d, yyyy" - full date, including the day of the week, displays as
*Sunday, March 08, 2015*.

For example, if you have a column of US dates in Excel and you need to export them to a .csv file for your UK based partner, you can convert the dates to the UK format, as a courtesy:

`=TEXT(A1,"mm/dd/yyyy")`

Some more formula examples and their results are shown below:

If your date entries display both dates and times and you want to change them to text strings exactly as they are, you included the following **time codes** in the format_text argument of the Excel TEXT function.

**Hours**:

- h - hours without a leading zero, as 0-23.
- hh - hours with a leading zero, as 00-23.

**Minutes**:

- m - minutes without a leading zero, as 0-59
- mm - minutes with a leading zero, as 00-59

**Seconds:**

- s - seconds without a leading zero.
- ss - seconds with a leading zero.

**Periods of the day:**

- AM/PM - displays as AM or PM.
- If not specified, 24-hour time format is used.

As you probably noticed, the **m** codes are used for months as well as minutes, and you might be curious how Microsoft Excel distinguishes between them. If you put "*m*" immediately after **h** codes (hours) or immediately before **s** codes (seconds), Excel understands you want to display minutes rather than a month. Yep, it's that simple : )

The TEXT function in Excel allows including both date and time codes in the `format_text`

argument, for example:

`=TEXT(A2,"dd/mm/yyyy hh:mm")`

If you want to convert the time portion only, then put only the time codes, like this:

`=TEXT(A2,"h:mm AM/PM")`

The results of your TEXT formulas may look similar to this:

In case you want to convert the current date to the text format, you can use the Excel TEXT function in combination with the TODAY function that returns the current date, for example:

`=TEXT(TODAY(), "dd-mmm-yyyy")`

The result of this formula would show up as 08-Mar-2015. If you prefer to display the resulting text string in some other format, please see the date codes discussed in Example 1.

Though the main destination of the TEXT function in Excel is converting numbers to text, it can also perform a reverse conversion, i.e. change text to date. For this, you simply add the double negation (--) to your TEXT formula.

For example, to convert a text string in cell A1 to date, you use the formula =--TEXT(A1,"mm/dd/yy") and then format the cell as a date.

As you've just seen, Excel's TEXT function makes a good job of converting dates to text. But if you are not a big fan of Excel formulas, you might like this solution better.

If you had a chance to read the previous part of our Excel dates tutorial, you already know how to use *Text to Columns* to change text to date. To convert dates to text strings, you proceed in the same way with the only difference that you choose **Text** instead of *Date* on the final step of the wizard.

If the default date format is not what you are looking for, you can jump right to the next solution that lets you convert dates to text strings in any format of your choosing.

If you don't mind the default format, then perform the following steps:

- In your Excel spreadsheet, select all of the dates you want to change to text.
- On the
*Data*tab, find the*Data Tools*group, and click**Text to Columns.**

- On step 1 of the wizard, select the
**Delimited**file type and click*Next*.

- On step 2 of the wizard, make sure none of the delimiter boxes is checked and click
*Next*.

- On step 3 of the wizard, which is the final step, select
**Text**under*Column data format*and click*Finish.*

That was really easy, right? The screenshot below demonstrates the result - dates converted to text strings in the default short date format set in your Windows Regional settings, which is "mm/dd/yyyy" in my case:

Another quick no-formula way to turn Excel dates into text strings is using Notepad or any other text editor. Unlike the Text to Columns wizard, it allows you to convert Excel date to text in any format of your choosing.

- In your Excel worksheet, format the dates exactly as you want the text strings to look like.
- Select all of the dates you want to convert and press Ctrl+C to copy them.

- Open Notepad or any other text editor, and paste the copied dates there.
- Notepad automatically converts the dates to the text format. Press Ctrl+A to select all text strings, and then Ctrl+C to copy them.
- Switch back to Microsoft Excel, select the column where you want to insert the text strings and apply the
**Text**format to it. To do this, press Ctrl+1 to open the*Format Cells*dialog and select**Text**on the*Number*tab.

- Finally, select the first cell where you want to insert the text strings and press Ctrl+V to paste them.

The following screenshot shows the result, with the original Excel dates in column B and text entries in column D. Please notice that the converted text strings reflect the original date format with absolute accuracy, except they are left-alighted, as all text values are supposed to be in Excel.

This is how you convert date to text in Excel. Next week we will explorer a few Excel functions to work with weekdays and days of the year. And in the meantime, you may want to check out the previous parts of our comprehensive tutorial to working with dates and times in Excel.

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 146 responses to "How to convert date to text in Excel with TEXT function and without formulas"

Hai,

Please help me to solve the following issue.

here I have certain shifts & their start time and end time.

how to get the hourly count for each shift for one day

for example:

Shift Start End 1st Hr - 2nd Hr - 3rd Hr - 4th Hr

Shift 1 00:30 12:30 32 32 32 32

Shift 2 01:30 13:30 32 48 48 48

Shift 1 02:30 14:30 32 48 56 56

Hi Wroxwiley,

Your table was mangled during posting and I am not sure I can follow you, sorry for this. If you can post the same question on our forum and attach a sample worksheet for better understanding, our support team will do their best to figure it out.

Please help me to convert date format to text format as like this

date format example 01/01/2015 say in cell no a1

i want to convert as follows 01012015 in cell no b1

Hi HABIBUL,

You can enter one of the following formulas in B1, depending on which time unit you want to come first - month or day:

=TEXT(A1,"mmddyyyy")

=TEXT(A1,"ddmmyyyy")

Dear Svetlana, I am using Excel 2010 v 14.0.473

When using the formula =text(A1,"ddd") I get "The formula you typed contains an error". A1 = 2016/11/17

Please help?

Hello Riaan,

It may happen if a value in A1 is a text string, not a date. To check this, select A1 and look at the Number Format box on the Home tab in the Number group. If it displays General or Text, you are dealing with a text string that looks like a date. In this case, you will need to convert text to date first.

Dear Svetlana, nope, the format is Date. Even if I point the formula to an empty cell, I get the same error. This does not seem right.

Even when I use Datevalue and point to my date field that is either formatted as text or date, I get #VALUE!

All other Excel formulas work perfectly.

Unfortunately I cannot post screen shots here of my spread sheets to prove my points to you.

Any suggestions?

Dear Riaan,

I am afraid it's not possible to determine the root of the problem without seeing your data. If you can send us your sample worksheet at support@ablebits.com, I will try to help.

Hello,

Please help me some time if i save the Excel totally text format is changing to Date format.

Now i need to change total Excel format Date format to Text format how can i change the format.

Hi;

I want to convert number of days to y m d.

Eg; 1092 days and have Excel convert that to 2years 11months 6days

Thankfull for quick replay.

Hi!

Because there are leap and non-leap years, and the number of days in each month varies from 28 to 31, we can only suggest the formula with a certain degree of approximation. I.e. 365 days are counted as a year, and 365/12 is counted as a month:

=CONCATENATE(IF(INT(A1/365)>0,INT(A1/365)&" years",""),IF(INT(MOD(A1,365)/(365/12))>0," "&INT(MOD(A1,365)/(365/12))&" months", "")," ",IF((A1-INT(A1/365)*365-INT(MOD(A1,365)/(365/12))*(365/12))>0,INT((A1-INT(A1/365)*365-INT(MOD(A1,365)/(365/12))*(365/12)))&" days",""))

Where A1 is the cell containing the number of days you want to convert.

For 1092 days, the formula returns 2 years 11 months 27 days.

Thanks so much it working :)

i want some more i dont have days in there i have only Date of Joing a one person then i want everything do in a one cell.

Syed,

If you want to display the difference between 2 dates, say between the Date of Joining (A1) and today's date, then you can use the DATEDIF function to calculate the date difference, and the formula is much simpler:

=DATEDIF(A1, TODAY(), "y") &" years, "&DATEDIF(A1, TODAY(), "ym") &" months, " &DATEDIF(A1, TODAY(), "md") &" days"

Hi:

Thanks to you awesome tutorial I was able to convert date to text, but know I want to convert that text value into something measurable MINUTES.

For example:

My (A2) cell value is 1d:03h:40m 1day + 3hours + 40 minutes (1*24*60)+(3*60)+40 =1660

Can you please advise how can I perform or execute this conversion from TEXT(Date) to NUMBER (Minutes)?

Any questions feel free to let me know.

Thanks in advance.

Hi!

1d:03h:40m is a very specific format, so the formula is also highly specific:

=VALUE(LEFT(A2,FIND("d",A2)-1))*24*60+VALUE(MID(A2,FIND("h",A2)-2,2))*60+VALUE(MID(A2,LEN(A2)-2,2))

Please note that it will work correctly only if hours and minutes always contain 2 digits, with leading zeros if needed.

Hi!

I tried so much to find a way that i could use the number of the week of the year and the day of the week and make it a number. For example if we are in the 34th week of the year and the day is Tuesday then the text should show 3402. If its the 24th week of the year and the day is Monday then the text should show 2301 . The first 2 digits are the week of the year, and the other 2 digits are the monday-tuesday etc. Could you pls help me?

I did it :) see you

Hi Please please help

I want to mark "P" if the time-value is <7:00:00 hrs

Hello Azhar,

Supposing your have a list of times in column A, you can use the following formula:

=IF(A1<TIMEVALUE("7:00:00"), "P", "")

how to convert date of birth in text 02/02/1966- Second February nineteen sixty six in Excel sheet pl what formula i have to write

Plese help me..

Sunil,

I don't know a formula that could do this. Most likely a macro is needed in this case.

sir,my birthday 06-04-1983 ko excel formula se convert word six april nineteen eghty three

Sir,Can you help me in converting date into wordssuch as 05.04.1995 into Fifth, April, Nineteen Ninety five

Hi,

Is there a formula to convert 18/09/2015 to "Weekday" or 6/09/2015 to "Weekend"?

Thanks,

Hi JP,

You can use the following formula:

=IF(WEEKDAY(A1, 2)<6, "Weekday", "Weekend")

Where A1 is the cell with a date.

Hi Svetlana,

Do you know of a way to increment months from a date value input so it'll automatically show up in correct month/year order in a table?

Example: I have 12 columns, A to L. In K2, I input 9/20/15 (as the Start Date).

Then for A5 to L5, I retrieve from K2 and it should automatically increment month/year (as needed).

A5 has formula of =IF(OR($K$2="",$L$2=""),"",TEXT($K$2,"mmm 'yy"))

The output for A5 is Sept '15

I want B5 to automatically become Oct '15, C5 to become Nov '15, D5 to become Dec '15, E5 to become Jan '16, and so on.

Any suggestions on how to do this?

Many thanks.

Good news... I figured it out. This is how I did it:

For B5, function is =IF(OR($K$2="",$L$2=""),"",TEXT(EDATE($K$2,1),"mmm 'yy"))

For C5, function is =IF(OR($K$2="",$L$2=""),"",TEXT(EDATE($K$2,2),"mmm 'yy"))

and so on.

Hello, i just want to know the formula if i input "date" from cell1, and on cell2, output will be the year only. Like for example if i input date 9/29/2015 on cell1, the output for cell2 must be "A" for year 2015. Then if year 2016, the output will be B, and so on. Thanks.

I WANT TO LEARN DATE CONVERT INTO WORDS SUCH AS 01/01/2005,ONE JANUARY TWO THOUSAND FIVE

Is there a way to sort the text dates in chronological order in a pivot once you've used the formula in the data?

I'm doing a rolling year oct-2014 through sept-2015 (displayed as 15-Sep with text formula) but when pivoting this the dates show as alphabetized (ie 14-dec,14-oct,14-nov,15-apr,15-aug and so on)

Is there a way to convert a birthday (01/15/2012) into number of months and days (45 months 1 day)? I need the number of months and days to keep a continuous count as of the current day.

Thanks.

Hi Patty,

You can use the following formula, where B2 is the cell containing a birthday date:

=DATEDIF(B2,TODAY(),"M") & " Months " & DATEDIF(B2,TODAY(),"MD") & " Days"

good evening Sir,

how to convert date of birth in text

02/02/1966- Second February nineteen sixty six in Excel sheet

pl what formula i have to write

Hi

I want formula For text Date in Excel like that

Date:20/10/2015

"Twenty ,Ten, Two Thousand Fifteen"

Thanks plz reply

Hi i i have 14/10/2015 11:01

14/10/2015 11:01

14/10/2015 11:01

14/10/2015 11:01

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

14/10/2015 11:02

i want to add am/pm to each one. the date is currently in text mode.

Hi Svetlana,

Wondering if you could help me. I have a date in cell AB5, in US english format (mm/dd/yyyy) and a formula =TEXT(AB5, "mmm/yy") in cell AX5. For some reason the formula returns the date in date format rather than my desired Mon/YY format- any ideas why this may be the case?

Best regards

Jon

Hi Svetlana,

Thanks for such an informative post.

Is there a way to construct a formula like the below to return nothing if the source cell is blank? Right now, it is returning Jan-OO for all empty cells.

=TEXT(N2;"mmm-yy")

Thanks,

Donal

Hi Donal,

Sure. You can use the IF function to check for blank cells, like this:

=IF(N2=""; ""; TEXT(N2;"mmm-yy"))

Cracked it!

=IF(ISBLANK(N2);"";(TEXT(N2;"mmm-yy")))

Ah - just saw your post; many thanks!

Hi Svetlana,

I hope you can help me. I have 9/1/2015 12:11:53 PM (cell A2) which i need to convert into text. I have used some of the idea above which is =text(A2,"dd/mm/yyyy hh;mm;ss"), some cell works but some cell doesn't.

Do you know is there any other ways to convert this? Thanks in advance.

Hi Yennie,

In the time part of your format, try replacing semicolons with colons, like this:

=TEXT(A2,"dd/mm/yyyy hh:mm:ss")

I have the same question as above.

date format example 01/01/2015 say in cell no a1

i want to convert as follows 01012015 in cell no b1

But when I tried what you recommended it didn't work.

=TEXT(A1,"mmddyyyy")

=TEXT(A1,"ddmmyyyy")

Is there another way? Thank you so much for the help!

Hi Tiffany,

I've just tried the formula on my sheet and it has worked fine. How exactly doesn't it work in your Excel? Does it return a wrong result or error? If the latter, what error?

How to convert Mon, 30 Nov, 2015 to 30-Nov-2015 format

Hi

I used some software for entry so I export my entries and worked for invoice date + 30 = schedule date

In our report 1 to 12 date its calculated but 13 to 31 dates calculation its not able and the answer its #value

Please help me how could solve this error

When I paste the column to notepad - it does not change it to text. When I paste it back in excel - it is still a date. I am using office 365

Hi there,

Please help me - I have this birth date that I want to change to text ie 19720101 should read 01 Jan 1972, how do I do that?

Thank you so much!

Thersia

Hello THERSIA,

From your example, it's not quite clear which of the two "01" in 19720101 is month and which is day. Assuming it is the "yyyymmdd" format, you can use one of the following formulas (where A1 contains 19720101):

To convert the number to a date:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

As the result, the formula returns a serial number representing the date. Now, select the formula cell, press Ctrl+1 and set the following date format: "dd mmm yyyy". Please see How to create a custom date format in Excel for the detailed steps.

To convert the number to a text string:

=TEXT((DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))), "dd mmm yyyy")

how do u create if formula for

if 06:00:00 to 9:00:00 should be P

after 11:00:00 AM should be HAL

Before 2 PM should be HAL again

Hi Svetlana,

When I use the Text function within a formula, it is still displaying a number. Any reason why it is doing it? Note that when I try the function in a standalone cell, its working perfectly fine.

Here's what I mean-

="select ric_code,exchange_code,expiry_date from smd_live_raw..t_ds_optfuture where expiry_date =TEXT(42587,"dd-mmm-yyyy")"

The number 42587 is being taken from a cell that contains a date.

Any help please? Thanks.

-Kishore

Please change general function after use this formula TEXT(42587,"DD MMM YY")

ans : 05 Aug 16

how to convert date of birth in text

01/07/1965- First july nineteen sixty five in Excel sheet

pl what formula i have to write.my mob.number is 7773876579

Hi Svetlana

how to convert date of birth in text

02/02/1966- Second February nineteen sixty six in Excel sheet

pl what formula i have to write

my mob.no is 7773876579

This article is really helpful, thank u so much Svetlanta...

Hi Mam! Could you help me convert "Sat, May 14, 2016" into "14/5/2016" format and separate "Sat" from the string when the former started with asterisk mark. Thanks in advance.

how to convert to date month year words in excel

15/06/2006i want to result fifteenth june two thoushand six please help me

27/5/2016

21/4/2016

29/5/2016

15/5/2016

not able to change this date format to mm/dd/yy

Hello Priti,

Select the cells, pres Ctrl+1 to open the Format Cells dialog, select Date under Category and choose the desired format. Or, select Custom under Category, and type mm/dd/yy in the Type box.

i want to know how to convert date into words(english) in excel 2003 or 2007

for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.

how to convert date for example 14/07/2008 in word " Fourteenth Jun Tow Thousand"

Hi my question is when I am use

=E1&" "&" "&E2&" "&TEXT(E3, "MMM YY ") formula.

I want to change the year from 2016 to 2015 but when copy that formula and past it into the next cell the year stay the same even when I put it as paste fill without formatting.

is it possible to covert a numeric date into complete Text format? e.g 13/02/2015 in thirteenth February Two thousand and fifteen.

i want to know how to convert date into words(english) in excel 2003 or 2007

for eg. 22.04.2010 - convert it into Twenty Two April 2010 or Twenty Two April Two Thousand Ten.

I have cells formated as text.now i wantto genrate todaydate but in text formate these cells does not read today date.plz help

How to use d(), m() and y() functions in excel

I would like to enter a ratio like "1:1" or "7:5" in a cell that has been previously formatted as Text. Excel 2013 constantly changes my entries to date/time formats. How hard is it for the geniuses in Redmond to understand that when I format a cell as Text I want whatever I enter in it to be displayed EXACTLY AS I HAVE ENTERED IT and NOT AS ANYTHING ELSE???????

Hi all,

I would like to convert date, i.e. 31-12-2015 into text format... like Thirty one - December - Two thousand fifteen.

Hi,

I would like to learn, how to convert 01.01.2016 to 01-01-2016 format.

Hello Rajeshkumar,

You can use Excel's Replace All feature to replace the dots (.) with dashes (-). The detailed steps with screenshots can be found here: How to convert text strings with custom delimiters to dates.

=SUBSTITUTE(A1,".","-")

that was very simple way of doing.

Is there no other option, using Text and columns??

Sure, you can use the Text to Columns feature too as demonstrated in this example:

Covert text to date with Text to Columns. I prefer 'Replace All' because it's faster, but it's up to you.

Another way to convert a date in mm.dd.yyyy (or dd.mm.yyyy) format to mm-dd-yyyy (or dd-mm-yyyy) is using the Substitute function:

=VALUE(SUBSTITUTE(A1, ".", "-"))

Hi Svetlana.

This blog is awesome. Thanks

I have a column D in an Excel sheet that shows date exported from a system in the format;

day/ month / yyyy as shown in examples below

29/07/2010

24/04/2010

9/7/2010

20/07/2010

15/08/2010

etc

I need to convert these dobs in to a 4 digit PIN code DDMM

so results from above will be:

2907

2404

0907

2007

1508

I've tried =TEXT(D2,"mmdd") but it keeps the slashes / and always retains the yyyy at the end

I've tried =TEXT(D2,"dd")&TEXT(D2,"mm") but same issues

A maths friend gave me this and it seemed to work for a while:

=IF(LEN(TEXT(100*DAY(D2)+MONTH(D2),0))=3,CONCATENATE("0",TEXT(100*DAY(D2)+MONTH(D2),0)),TEXT(100*DAY(D2)+MONTH(D2),0))

I'm using Excel 2010. Help please.

Thank you in anticipation

Hi Mark,

This simple formula works just fine for me:

=TEXT(D2, "ddmm")

You can check it in a new empty sheet by typing a few dates manually in your default date format, not copy/paste from the existing sheet. If it works with the regular dates typed manually, then most likely the problem is with the exported dates, e.g. they may be text strings that look like dates. To check this, select any cell with a date, and look at the Number Format box on the Home tab > Number group. If it shows Text or General, you are dealing with text strings, not dates. In this case, you can simply extract the 1st, 2nd, 4th, and 5th character using this formula:

=LEFT(D2,2)&MID(D2,4,2)

Please note, the above formula will work only if all the dates are in the same format, i.e. dd/mm/yyyy.

If there are dates formatted as d/m/yyyy (without leading zeros), you will need to convert text strings to dates first, and then apply =TEXT(D2, "ddmm") to the converted dates.

OK - thanks for confirmation & the extra info

Hi Svetlana,

I am trying to copy a either an entire worksheet or a range of data from one workbook to another. I can do this successfully manually, by selecting all the cells with data in them and pasting into a newly created worksheet (Date format is maintained). However when I try to do this via VBA any date with a day greater than 12 gets converted to text [US (m/dd/yyyy)/Australian (dd/mm/yyyy) format change]. I've tried formating the cells first to US format dates but this doesn't help. Any sugestions?

hi.

how to change date of birth figure in to words in ms excel 2010. for Example 4/3/2002.4th May Two Thousand Two

Hi,

Can you please help me to convert the date format 24/01/2017 in "Twenty-Forth January Two Thousand Seventeen"..??

Please let me know.. its very urgent

How to convert 'January 18th 2017, 12:04 am' to '18/01/2017 12:04 AM' in Excel?

Is there a way to have a cell pull date data from one cell (ex. May 15, 2017) to say 'Mid May' instead?

How do you convert 2015-JAN-10 to read 01-10-15 in excel?

Dear Svetlana,

When I write 2013 on my excel sheet I have the problem that excel sees it as 1905, I have tried to use =Year(2013), change format, use text to columns but doesn't seem to found a way to correct this, could you help me?

Best regards

Hi Svetlana,

I am trying to convert several weeks of date formatted eg;Wednesday, 1 March 2017 to straight text 'WED" for use in pivot table /graphs.

Custom formatting using "ddd" still retains the "number" value, and using a notepad to convert will not work as I am using Macros.

Thank you in advance.

I am trying to make the formula that in cell A1 has a date and other cell B1 specific date or text i.e "Not Selected" and result in other cell C1 should be the month and days while cell B1 can be counted the today date. I could not make that formula. Please help me. Thank you in advance.

Hello! I require conversion of date of birth (25/09/1998) to date of birth in words as "Twenty fifth September Nineteen Ninety Eight" in Excel. Please help me.

Hi,

Anybody help me to change this time format to words ?

(55:56:14 , it shows 7 hours, 56 minutes and 14 Seconds)

it should be 55 hours.... ...

INT(D179)&" Days," & HOUR(N179)&" hours, " &MINUTE(N179)&" minutes and "&SECOND(N179)& " Seconds"

How do I create a formula when a specific text is typed in a cell, the current time & date will appear in an assigned cell?:

A2 = YES

B2 = (display current time and date)

A2 = NO

B2 = (will be blank)

Thanks!

=IF(A1="yes",NOW()," ")

change the format for b1 so that it shows only time

ie by going to format cells selecting time and select the appropriate one you required

how to convert date to text in excel.for examble 10/03/2000 is convert to ten-march-two thousand like this

Not possible ,

you can do this formula

=TEXT(a2,"ddd mmmm yyyy") the output you would receive would be Tue October 2000

Hello,

Apologies if you have covered this above, I have tried a bunch of your instructions that look applicable but none seem to work for me.

I would like to type the following into a cell for today's date for example: 2905, and then for it to change to this: 29/05/17. Or if it needs to have the same amount of digits, type this: 290517 to get this 29/05/17.

If I want it in the same cell, can this be done by conditional formatting? If not, can the formula go in a cell away from the current columns (so I don't need to have two columns next to each other just for the conversion)?

Your help is greatly appreciated. If you could spell it out step by step that would be great, I am a bit useless. i.e: "click in A2 where you have typed the date as 2905, then click in cell D2..." :)

Thanks! Brooke

Hi

I am using "Text to Column" feature to convert date. however it is not converting the 29 February 2017.

Grateful if you please guide.

Regards,

Taufiq

Hi,

I am trying to return the number of days between the 2 dates

AE (6/9/2017 17:30 and AD (2/7/2017 9:35)

Formula: =TEXT(AE6-AD6,"dd,hh:mm:ss")

result: 01,07:54:45

How can I return the 3 digits?

I tried this formula:

=INT(AE8-AD8)&"Days" & TEXT(AE8-AD8,"H""hrs""m""mins""")

but then this does not seem to be in an order if I am trying to filter quickly.

Could you please advise on this?

Thanks!

How to convert date of birth in words. Please help in excel.

thanks

how to convert date of birth in text

01/07/1965- First july nineteen sixty five in Excel sheet

pl what formula i have to write.my mob.number is 9423818133

Hi Svetlana,

I see you are trying to help users with Excel TEXT function used to format time. Well, I've faced problem in international environment, where different users may have different localization of Excel, as well as different local setting of Windows. It cause funny thing, that date formating in Excel TEXT function vary by local setting, e.g. "YYYY-MM-DD" in whatever English, "RRRR-MM-DD" in Czech, "VVVV-KK-PP" in Finish, etc. Excel is not converting it based on local setting, so formula created in one local setting, doesn't work in others. I understand it as MS Excel bug, but it doesn't help me to solve it.

Have you ever seen this and are you aware of any solution/workaround, which may work? I'd appreciate any tip here.

My workaround now is this formula: =YEAR(source date cell)&"-"&MONTH(source date cell)&"-"&DAY(source date cell). I'm not fully happy with the solution, as weakpoint is, it may result in single digit month and/or single digit day, which I want to keep as double digit (like 2017-09-01, instead 2017-9-1). Maybe it can be solved by some IF and counting of characters, but it's going to be way too complex, comparing to =TEXT(source date cell; "YYYY-MM-DD").

Any idea or tip here is very welcomed.

Regards, Ales

in sell A1 - TEXT 18102017 please convert this as 18/10/2017 in cell B1

PL PROVIDE SUITABLE FORMULAS FOR CONVERSION USING =TEXT() FUNCTION

REGARDS

I appreciate this, thanks. 1 question about the recommendations:

This does all I need EXCEPT I want to change the short date format the text comes through in. I need it to be yyyy/mm/dd. Do I have to change my regional settings or is there another way to do that?

hi please help me!

how to do this i have 3 column with date and i want it if there is a date display the date but if its blank display the other date in the column.

thanks so much in advance!

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Hi hello pls anyone help me in this issue i need convert dates format already in in excel from 15.04.2017 to 15-04-2017

all the dates are entered with . (dots) only I'm unable to change the format kindly suggest any idea

ssrm

An interesting problem you have posed. I cannot be certain that all of the information needed to answer you is available in your question but potentially there are solutions.

Firstly you need to know whether the dates like 15.04.2017 are true dates or text.

Secondly, you need to consider whether the format (appearance) as 15.04.2017 is simply a result of the locale you are using and if so what the default date format is for that locale.

I presume, because you ask about displaying them as 15-04-2017 that you have already tried to format the dates to display differently using the Format|Cells|Dates options and have found that they do not respond. This suggests that the dates are text not true dates. The locale that is used then becomes irrelevant. Text is literal (wysiwyg).

So, if the dates are not true dates but simply text, David Carter provides a quite simple solution at https://www.accountingweb.co.uk/tech/excel/exporting-data-from-sage-line-50-into-excel-1-by-david-carter

I shall not reproduce all that he says here. If when you try to access it you find that it has been taken down, post an email address here and I shall send a copy to you. In brief, it is designed for a column of dates, and uses the Excel Text to columns functionality, so there is an assumption that you have a column of 'dates' which Excel presently treats as text. The solution converts the text to real dates.

Once you have true dates you can then choose to format the cells as dates in whatever format is appropriate. If the file is to be shared across multiple locales then you may rather than using the standard date formats available for particular locales, which can result in differences such as you see (15.04.2017 which may appear in other locales as 15/04/17, 04/15/17 or even 17/04/15) set up a custom date format.

For the custom date format, sadly, I cannot help. If we get a good reply to my post below (73), we may then know how to set one up, but as far as I can see Excel does not respond helpfully across locales, so eg dd-mm-yyyy -> 'dd-00-yyyy' in a German locale, and dd-MM-yyyy -> 'dd-05-yyyy'. We have to use TT-MM-JJJJ, which is of little help if you are in a different locale.

Finally, I have just noticed that you can select a locale for the date format using Format|Cells|Dates, which will allow you to apply a standard format to display 15-04-2017. English (India), possibly others, contains this format. Whether this works if the file is later transferred to a different locale, I am sorry I do not know the answer to that.

I hope this helps you

Stuart

A useful article with many good hints and tips but sadly did not address my specific issue.

I have in Excel a cell '_p_end' which contains a date, say 31/05/2018, which is entered by a user. This is converted to text by =TEXT(_p_end,"dd/mm/yy") to be used in later reports.

Sadly, some of our users do not use English as their default language. In Excel, for eg a German user, the date displays as 31.05.2018 but it is still a date and may be manipulated as such. However

TEXT(_p_end,"dd/mm/yy") = #VALUE!

"dd/mm/yy" is not a valid date format in German.

How can we overcome this?

Thank you

Stuart

GREAT FORMULA !!

Hi Svetlana,

i have the following problem:

I can not change the formatting of date that was downloaded form txt file(notepad). From this one 22-10-2018 23:01:01 d/m/yyyy hh:mm:ss to 10-22-2018 23:01:01 m/d/yyyy hh:mm:ss. I even copy that to different sheets but it doesn't work.

Could you please help with that

Regards,

Seymur

HI. I have a spreadsheet where the date has been supplied in dd.mm.yy format - Using dots.

I want to change this to just using the Month. I have tried using the formula =SUBSTITUTE(E2,".","/") but then I can't change than data to only show month?

Please could you help

Hi Svetlana,

I have the following problem:

I want to subtract the two rows value if both are same date i.e I want to calculate the employee working hours starting time - ending time on a same day ,The values are in diff rows, if only one stamp of employee is there then make it error

thanks in Advance Please could you help

Hi Svetlana,

If I need to convert from text to data the information below, what formula do I have to use?

10/8/2018

10/10/2018

8/8/2019

I have to mention that I have all this dates in the same column.

Thank you in advance!

Roxana

3/14/2019 9:30 AM i want this as 14-03-2019 09:00:00

3/31/2019 6:39 PM - 31-03-2019 18:39:00

Hi Svetlana Cheusheva,

Thanks for the notes, it helped me lot.

Hello

I want to change dight into Text.

Ex:7:30 to convert into 7hrs30min.

Please let me as soon as possible.

Its needed for me!

i want the format SEP 19 in csv from 01-09-2019

date to text format

how to convert , pls tell me

I am not able to change 10-MAY-89 to another format like yyyymmdd.

The format of the cell is standard and whenever I try to modify it to any date format, nothing happens.

Thanks for your help,

Any

date show as "20191231".how can convert it as "YYYY/MM/DD"

Hi,

i have the following problem.

When exporting my contacts from Linkedin, I receive a .csv file. I open the file in Excel (2013). One column of this file is filled with dates that look like this:

10/27/19, 6:13 AM

This format is text as it sits on the left of the cell.

Manually, i delete the comma and the time. In this example i come up with:

10/27/19

I can't convert this date with the function DATEVALUE. I have a VALUE ERROR.

I tried to used the "Text to column" Wizard, no results.

I think that my problem comes from the fact I have a date:

in a US format

AND

in text format.

Do you have any idea you to solve this?

Interestingly, i have a like macro my PC, a datepicker that manages to convert my american text date to a UK date. But this means i would have to click all the dates in my column one by one, which is not convenient.

Thx

JG

I WANT YYYYMM DATE NOT WORKING THE TEXT COMMAND 199707 20/07/1997 I WANT 199707 HOW I CAN DO

Hai mam,

in a file some date are entered but file sent to one system to another some date should be shuffled (it have 2000 entries in a column) .

ie.

10-03-19

10-03-19

10-03-19

04-10-19

10-08-19

04-10-19

10-10-19

10-08-19

in this file "10" represent the month. i want this file in dd/mm/yy format. please support me.

Thanks alot for sharing.

Hi,I have Mar20. Is it possible to convert it to 3/31/2020, which is the last date of the month?

Thank you so much if anyone can help.

Hello Jenny!

Please try the following formula

=EOMONTH(A1,0)

Note! Don't forget to set the Date format for cell E1.

Hope you’ll find this information helpful.

It works! Thank you so much!

Hi,

Could you please share me the process to convert below text to date:

07 May 2020 17:23:47:000

Hello Tanvir!

If I understand your task correctly, the following formula should work for you:

=DATEVALUE(SUBSTITUTE(A5,TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),"")) + TIMEVALUE(LEFT(TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",20)),20)),8))

you can try to use Custom Format. Please go to Format Cells, choose Number -> Custom Format and set the followig format:

dd mmmm yyyy hh:mm:ss

I hope my advice will help you solve your task.

Thank you !!! It helped me a lot <3

I would like to convert a text string that contains date format MMM-YY such as "Mar-19" and "Jun-18" to be formatted in MMM-YY format, but I do not want it to assume that the date is of the current year.

Example:

Current year is 2020, my cell contains text "Mar-19", but using =Text(A1,"MMM-YY") returns value "Mar-20".

I would like this to return "Mar-19".

My hope is to use this text in a subtraction formula to find how many months from this start date to current date.

Hello Daniel!

To convert text "Mar-19" to date you can use this formula

=DATEVALUE("01-"&A2)

I hope my advice will help you solve your task.

I am creating a spreadsheet with document name, document type, and date columns. In the final column I am using the "concatenate" function to piece all this data together in order to create a file name (in other words, I'm creating a naming convention for storage of all the files listed on this spreadsheet.

For sorting purposes, I want the year first, then the month, then the date. I can force the date fields to reflect this by using the custom format option. However, when I use the "text" function to bring that date over into the file name column, the date is still converting into Excel's serial number for that date. (2020.05.05 converts to 43956).

How can I get the date to carry over properly?

Here is my formula, with G17 being the date field carrying over into this column:

=IF(D17=" ",(CONCATENATE(F17,"_",TEXT(G17,"yyyy-mm-dd"),"_",TEXT(H17,"00000000"),"_",I17)),(IF(D17"",(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17,"_",D17)),(CONCATENATE(F17,"_",G17,"_",TEXT(H17,"00000000"),"_",I17)))))

Hello!

Replace twice in your formula

,G17,

with

,TEXT (G17, "yyyy-mm-dd"),

thanks for your online support it's very helpful.

Thanks,

Kumar.S

Please help to convert the below mentioned date Sep 11 2020 11:52AM

into DD-MM-YYYY HH:MM:SS format

Hello!

If I understand your task correctly, the following formula should work for you:

=IF(MID(A1,18,2)="PM",DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3) & "-"&MID(A1,8,4))+TIMEVALUE(MID(A1,13,5))+0.5, DATEVALUE(MID(A1,5,2) & "-"&LEFT(A1,3)&"-"&MID(A1,8,4)) + TIMEVALUE(MID(A1,13,5)))

Set a custom date and time format in the cell DD-MM-YYYY HH:MM:SS

Please help to convert the below mentioned date

DD-MM-YYYY

into DD.MM format

tnx

Hello!

I hope you have studied the recommendations in the above tutorial.

Please try the following formula:

=TEXT(B1,"dd.mm")

I want to convert the date to complete text like, 1-Jan-2021 to First January Two Thousand and Twenty One.

Please can you guide in this.

Hello!

To write the date in words, you need to use the VBA macro.

How to convert "03/25/1997" into "March Twenty-Fifth, Nineteen Hundred Ninety-Seven"

how do I convert :

01/07/2012 to 1st July, 2012

02/07/2012 to 2nd July, 2012

03/07/2012 to 3rd July, 2012

04/07/2012 to 4th July, 2012

Meaning for:

01 it is 1st

02 it is 2nd

03 it is 3rd

04 it is 4th

Hello!

To format date with “st”, “nd”, “rd”, “th” you can use the formula

=CONCATENATE(DAY(D2)&LOOKUP(DAY(D2),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"})," ",TEXT(D2,"mmmm, yyyy"))

Day(D2) – returns the day of the month in number

LOOKUP(DAY(D2),{1,”st”;2,”nd”;3,”rd”;4,”th”;21,”st”;22,”nd”;23,”rd”;24,”th”;31,”st”}) – returns the corresponding suffix of the day

I hope my advice will help you solve your task.

1) Apples- Fruits, $100 (existing typed in one cell)

How to formula this into 3 dfferent cell to Cell A1,B1,C1

2) under one cell with 12345

How can i separate it into Cell A1,B1,C1,D1,E1

Thanks

Hello!

Here is the article that may be helpful to you: How to split cells in Excel: Text to Columns, Flash Fill and formulas.

I hope it’ll be helpful.