4/7/2018 How to change Excel date format and create custom formatting

HOME PRODUCTS   DOWNLOADS   SUPPORT   BLOG ABOUT US PURCHASE  

How to change date format in Excel and create custom formatting

Mar by Svetlana Cheusheva | updated on March 27, 2018 253 Comments

11

The rst part of our tutorial focuses of formatting dates in Excel and explains how to set the default Custom Search
date and time formats, how to change date format in Excel, how to create custom date formatting,
and convert your dates to another locale.

Along with numbers, dates and times are the most common data types people use in Excel. Excel: featured articles
However, they may be quite confusing to work with, rstly, because the same date can be
Merge multiple sheets into one
displayed in Excel in a variety of ways, and secondly, because Excel always internally stores
dates in the same format regardless of how you have formatted a date in a given cell. Compare 2 Excel les/sheets for
di erences
Knowing the Excel date formats a little in depth can help you save a ton of your time. And this
is exactly the aim of our comprehensive tutorial to working with dates in Excel. In the rst part, Compare 2 columns in Excel for
we will be focusing on the following features: matches and di erences

Merge 2 columns in Excel

Excel date format
Merge Excel rows without losing
How to convert date to number in Excel
data
Default date format in Excel
Excel INDEX / MATCH functions
How to change date format in Excel instead of VLOOKUP

How to convert date format to another locale How to nd duplicates in Excel

How to create a custom date and time formats Change text case to UPPERCASE,
Excel date format not working - xes and solutions lowercase, Proper Case

CONCATENATE in Excel:
combine text strings, cells and
Excel date format columns

How to Vlookup to return

Before you can take advantage of powerful Excel date features, you have to understand how
multiple matches in Excel
Microsoft Excel stores dates and times, because this is the main source of confusion. While you
would expect Excel to remember the day, month and the year for a date, that's not how it 3 ways to remove spaces
works... between words / numbers in
Excel cells
Excel stores dates as sequential numbers and it is only a cell's formatting that causes a
number to be displayed as a date, time, or date and time. Excel VLOOKUP tutorial for
beginners - syntax and formula
examples
Dates in Excel
How to remove empty rows in
All dates are stored as integers representing the number of days since January 1, 1900, which
Excel
is stored as number 1, to December 31, 9999 stored as 2958465.
COUNTIF in Excel - count if not
In this system: blank, greater than, duplicate or
unique
2 is 2-Jan-1900

3 is 3-Jan-1900
Outlook: featured articles
42005 is 1-Jan-2015 (because it is 42,005 days after January 1, 1900)
Merge duplicate contacts in
Outlook
Time in Excel
Remove duplicate emails in
Times are stored in Excel as decimals, between .0 and .99999, that represent a proportion of Outlook quickly and safely
the day where .0 is 00:00:00 and .99999 is 23:59:59.
Сreate and use e-mail templates
For example: in Outlook

0.25 is 06:00 AM

0.5 is 12:00 PM

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 1/27
4/7/2018 How to change Excel date format and create custom formatting
0.541655093 is 12:59:59 PM
Excel formulas
Dates & Times in Excel Excel PivotTables Excel charts
Excel stores dates and times as decimal numbers comprised of an integer representing the Excel Vlookup
date and a decimal portion representing the time.
Excel duplicates
For example: Merge data in Excel

1.25 is January 1, 1900 6:00 AM

Excel formatting
Excel consolidation Excel date
42005.5 is January 1, 2015 12:00 PM
Excel time Excel tutorials
Excel tips Google Sheets
How to convert date to number in Excel Outlook templates
If you want to know what serial number represents a certain date or time displayed in a cell,
you can do this in two ways.

1. Format Cells dialog Sign in

Select the cell with a date in Excel, press Ctrl+1 to open the Format Cells window and switch to Register
the General tab. Log in

If you just want to know the serial number behind the date, without actually converting date to
number, write down the number you see under Sample and click Cancel to close the window. If
you want to replace the date with the number in a cell, click OK.

2. Excel DATEVALUE and TIMEVALUE functions

Use the DATEVALUE() function to convert an Excel date to a serial number, for example
=DATEVALUE("1/1/2015").

Use the TIMEVALUE() function to get the decimal number representing the time, for example
=TIMEVALUE("6:30 AM").

To know both, date and time, concatenate these two functions in the following way:

=DATEVALUE("1/1/2015") & TIMEVALUE("6:00 AM")

Note. Since Excel's serial numbers begins on January 1, 1900 and negative numbers aren't

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 2/27
4/7/2018 How to change Excel date format and create custom formatting
recognized, dates prior to the year 1900 are not supported in Excel.

If you enter such a date in a sheet, say 12/31/1899, it will be a text value rather than a date,
meaning that you cannot perform usual date arithmetic on early dates. To make sure, you can
type the formula =DATEVALUE("12/31/1899")in some cell, and you will get an anticipated
result - the #VALUE! error.

Default date format in Excel

When you work with dates in Excel, the short and long date formats are retrieved from your
Windows Regional settings. These default formats are marked with an asterisk (*) in the Format
Cell dialog window:

The default date and time formats in the Format Cell box change as soon as you change the
date and time settings in Control Panel, which leads us right to the next section.

How to change the default date and time formats in Excel

If you want to set a di erent default date and/or time formats on your computer, for example
change the USA date format to the UK style, go to Control panel and click Region and
Language. If in your Control panel opens in Category view, then click Clock, Language, and Region
> Region and Language > Change the date, time, or number format.

On the Formats tab, choose the region under Format, and then set the date and time
formatting by clicking on an arrow next to the format you want to change and selecting the
desired one from the drop-down list:

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 3/27
4/7/2018 How to change Excel date format and create custom formatting

Ultimate Suite for Excel Professionals

Tip. If you are not sure what di erent codes (such as mmm, ddd, yyy) mean, click the "What
does the notation mean" link under the Date and time formats section, or check the Custom
Excel date formats in this tutorial.

If you are not happy with any time and date format available on the Formats tab, click the
Additional settings button in the lower right-hand side of the Region and Language dialog
window. This will open the Customize dialog, where you switch to the Date tab and enter a
custom short or/and long date format in the corresponding box.

How to quickly apply default date and time formatting

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ in Excel 4/27
4/7/2018 How to change Excel date format and create custom formatting

How to quickly apply default date and time formatting in Excel

Microsoft Excel has two default formats for dates and time - short and long, as explained in
default Excel date format.

To quickly change date format in Excel to the default formatting, do the following:

Select the dates you want to format.

On the Home tab, in the Number group, click the little arrow next to the Number Format box,
and select the desired format - short date, long date or time.

If you want more date formatting options, either select More Number Formats from the drop-
down list or click the Dialog Box Launcher next to Number. This will open a familiar Format Cells
dialog and you can change date format there.

Tip. If you want to quickly set date format in Excel to dd-mmm-yy, press Ctrl+Shift+# . Just
keep in mind that this shortcut always applies the dd-mmm-yy format, like 01-Jan-15,
regardless of your Windows Region settings.

How to change date format in Excel

In Microsoft Excel, dates can be displayed in a variety of ways. When it comes to changing date
format of a given cell or range of cells, the easiest way is to open the Format Cells dialog and
choose one of the prede ned formats.

1. Select the dates whose format your want to change, or empty cells where you want to insert
dates.

2. Press Ctrl+1 to open the Format Cells dialog. Alternatively, you can right click the selected
cells and choose Format Cells… from the context menu.

3. In the Format Cells window, switch to the Number tab, and select Date in the Category list.

4. Under Type, pick a desired date format. Once you do this, the Sample box will display the
format preview with the rst date in your selected data.

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 5/27
4/7/2018 How to change Excel date format and create custom formatting

5. If you are happy for the preview, click the OK button to save the format change and close
the window.

If the date format is not changing in your Excel sheet, most likely your dates are formatted as
text and you have to convert them to the date format rst.

How to convert date format to another locale

Once you've got a le full of foreign dates and you would most likely want to change them to
the date format used in your part of the world. Let's say, you want to convert an American date
format (month/day/year) to a European style format (day/month/year).

The easiest way to change date format in Excel based on how another language displays dates
is as follows:

Select the column of dates you want to convert to another locale.

Press Ctrl+1 to open the Format Cells

Select the language you want under Locale (location) and click OK to save the change.

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 6/27
4/7/2018 How to change Excel date format and create custom formatting
If you want the dates to be displayed in another language, then you will have to create a
custom date format with a locale code.

Creating a custom date format in Excel

If none of the prede ned Excel date formats is suitable for you, you are free to create your
own.

1. In an Excel sheet, select the cells you want to format.

2. Press Ctrl+1 to open the Format Cells dialog.

3. On the Number tab, select Custom from the Category list and type the date format you want
in the Type box.

Tip. The easiest way to set a custom date format in Excel is to start from an existing
format close to what you want. To do this, click Date in the Category list rst, and select
one of existing formats under Type. After that click Custom and make changes to the
format displayed in the Type box.

When setting up a custom date format in Excel, you can use the following codes.

Code Description Example (January 1, 2005)

Month number without a leading

m 1
zero

mm Month number with a leading zero 01

mmm Month name, short form Jan

mmmm Month name, full form January

J (stands for January, June and

mmmmm Month as the rst letter
July)

d Day number without a leading zero 1

dd Day number with a leading zero 01

ddd Day of the week, short form Mon

dddd Day of the week, full form Monday

yy Year (last 2 digits) 05

yyyy Year (4 digits) 2005

When setting up a custom time format in Excel, you can use the following codes.

Code Description Displays as

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 7/27
4/7/2018 How to change Excel date format and create custom formatting
h Hours without a leading zero 0-23

hh Hours with a leading zero 00-23

m Minutes without a leading zero 0-59

mm Minutes with a leading zero 00-59

s Seconds without a leading zero 0-59

ss Seconds with a leading zero 00-59

Periods of the day

AM/PM (if omitted, 24-hour time format is AM or PM
used)

Note. If you're setting up a custom format that includes date and time values and you use
"m" immediately after "hh" or "h" or immediately before "ss" or "s", Microsoft Excel will
display minutes instead of the month.

When creating a custom date format in Excel, you can use a comma (,) dash (-), slash (/), colon (:)
and other characters.

For example, the same date and time, say January 13, 2015 13:03, can be displayed in a various
ways:

Format Displays as

dd-mmm-yy 13-Jan-15

mm/dd/yyyy 01/13/2015

m/dd/yy 1/13/15

dddd, m/d/yy h:mm AM/PM Tuesday, 1/13/15 1:03 PM

ddd, mmmm dd, yyyy

Tue, January 13, 2015 13:03:00
hh:mm:ss

How to create a custom Excel date format for another locale

If you want to display dates in another language, you have to create a custom format and pre x
a date with a corresponding locale code. The locale code should be enclosed in [square
brackets] and preceded with the dollar sign ($) and a dash (-). Here are a few examples:

[$-409] - English, Untitled States

[$-1009] - English, Canada

[$-407] - German, Germany

[$-807] - German, Switzerland

[$-804] - Bengali, India

[$-804] - Chinese, China

[$-404] - Chinese, Taiwan

You can nd the full list of locale codes on this blog.

For example, this is how you set up a custom Excel date format for the Chinese locale in the
year-month-day (day of the week) time format:

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 8/27
4/7/2018 How to change Excel date format and create custom formatting

The following image shows a few examples of the same date formatted with di erent locale
codes in the way traditional for the corresponding languages:

Excel date format not working - xes and solutions

Usually, Microsoft Excel understands dates very well and you are unlikely to hit any roadblock
when working with them. If you happen to have an Excel date format problem, please check
out the following troubleshooting tips.

A cell is not wide enough to t an entire date

If you see a number of pound signs (#####) instead of dates in your Excel worksheet, most
likely your cells are not wide enough to t the whole dates.

Solution. Double-click the right border of the column to resize it to auto t the dates.
Alternatively, you can drag the right border to set the column width you want.

Negative numbers are formatted as dates

In all modern versions of Excel 2013, 2010 and 2007, hash marks (#####) are also displayed
when a cell formatted as a date or time contains a negative value. Usually it's a result returned
by some formula, but it may also happen when you type a negative value into a cell and then
format that cell as a date.

If you want to display negative numbers as negative dates, two options are available to you:

Solution 1. Switch to the 1904 date system.

Go to File > Options > Advanced, scroll down to the When calculating this workbook section, select
the Use 1904 date system check box, and click OK.

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 9/27
4/7/2018 How to change Excel date format and create custom formatting

In this system, 0 is 1-Jan-1904; 1 is 2-Jan-1904; and -1 is displayed as a negative date -2-Jan-

1904.

Of course, such representation is very unusual and takes time to get used to, but this is the
right way to go if you want to perform calculations with early dates.

Solution 2. Use the Excel TEXT function.

Another possible way to display negative numbers as negative dates in Excel is using the TEXT
function. For example, if you are subtracting C1 from B1 and a value in C1 is greater than in B1,
you can use the following formula to output the result in the date format:

=TEXT(ABS(B1-C1),"-d-mmm-yyyy")

You may want to change the cell alignment to right justi ed, and naturally, you can use any
other custom date formats in the TEXT formula.

Note. Unlike the previous solution, the TEXT function returns a text value, that is why you
won't be able to use the result in other calculations.

Dates are imported to Excel as text values

When you are importing data to Excel from a .csv le or some other external database, dates
are often imported as text values. They may look like normal dates to you, but Excel perceives
them as text and treats accordingly.

Solution. You can convert "text dates" to the date format using Excel's DATEVALUE function or
Text to Columns feature. Please see the following article for full details: How to convert text to
date in Excel.

This is how you format dates in Excel. In the next part of our guide, we will discuss various

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 10/27
4/7/2018 How to change Excel date format and create custom formatting
ways of how you can insert dates and times in your Excel worksheets. Thank you for reading
and see you next week!

You may also be interested in:

How to conditionally format dates in Excel

How to insert dates in Excel - add today's date, auto ll a column with dates

How to calculate days between dates in Excel

253 Responses to "How to change date format in Excel and

create custom formatting"
← Older Comments

1 Nitin says:
September 22, 2016 at 2:18 pm

Hello,
I am looking to convert 14/09/2016 09:47:06 and 2/9/2016 12:01:21 PM these two date
format, listed in single column, to format "dd/mm/yyyy", in separate column.
I am able to covert second format using functions like TRUC/TEXT etc. but getting error
"#VALUE" wherever rst format is listed in that column.

Would appreciate prompt help.

TIA
Nitin Y.

Reply

2 rihanna says:
September 28, 2016 at 9:19 am

i was calculating for days left between two dates but i wanted it to give only the number of
days not the month it keeps giving me until number 31 and then goes back to counting
from one .if it is left 65 days i wanted it to give me 65 days but it gives me 4 or 3 so any
way to x it in le format dialog box

Reply

3 Dan Malone says:

September 28, 2016 at 11:45 pm

How can I change the date format of "07/22/2018" into "2018-07-22"? I have tried right
click, format but the slashes wont change into dashes. :/

Reply

Svetlana Cheusheva says:

September 29, 2016 at 8:23 am

Hello Dan,

Select the cell, and Press Ctrl+1 to open the Format Cells dialog. On the Number tab,
select Custom from the Category list and type the following format code in the Type box:
yyyy-mm-dd

Reply

Amanda says:
February 27, 2018 at 5:48 pm

Hi, I have tried this in my CSV le and it does work, but once I save it and reopen the
le, it has converted it back to MM/DD/YYYY. What can I do so that when I do the
Custom and change to YYYY-MM-DD it keeps it once saved?

Reply

4 Marcellin Habimana says:

October 4, 2016 at 10:16 am

Hi,

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 11/27
4/7/2018 How to change Excel date format and create custom formatting
I want to change the date format for my data from 10/11/2012 14.30.00 to 10/11/2012
14:30:00
Thank you in advance

Reply

Svetlana Cheusheva says:

October 4, 2016 at 2:37 pm

Hi Marcellin,

The easiest way is to use Excel's Find and Replace feature:

- Select all cells with dates that you want to reformat.

- Press Ctrl+H to open Find and Replace.
- Type a dot (.) in the "Find what" box, and colon (:) in "Replace with".
- Hit Replace All.

Done :)

Reply

5 srikanth says:
October 7, 2016 at 12:50 am

i want to conver the date formt "20160627T065908.000 GMT to dd/mm/yyy hh:mm:ss

how can i achive this fuctionality ? please suggest asap

Reply

6 DKS says:
October 12, 2016 at 5:44 am

I have Date data where it contain both type of date string

26 08 2016
25/8/16
want to apply and convert it into one format.

Reply

7 gnsh says:
October 18, 2016 at 7:22 am

how to change my default century year in excel...i cant read before 1900 year in poi

Reply

8 Terry says:
October 19, 2016 at 9:16 pm

I need to show Century in my date format, but I'm not understanding how to do this.
Example:

Format is CYYMMDD
C=Century
Years 2000-2099 C=1
Years 1900-1999 C=0

If date is 10/15/2016 on excel spreadsheet must show as: 1161015

If date is 12/31/2039 on spreadsheet must show as: 1391231

Reply

9 saeed says:
November 1, 2016 at 6:23 am

I need a date and time calculation. For example 11-1-2016 is current date previous date
23-03-1992 is equal to =23 years and 3 months and days 23
its use for age calculation ,,.
please help me .

Reply

10 Saurabh says:
November 1, 2016 at 1:15 pm

I am trying to write "08 - 16" which denotes "8 to 16" range but it is automatically

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 12/27
4/7/2018 How to change Excel date format and create custom formatting
converting 08 - 16 into August 2016 date format which I dont want. Please help me on how
to keep the format intact.

Reply

Svetlana Cheusheva says:

November 1, 2016 at 2:09 pm

Hi Saurabh,

Type it with the preceding apostrophe like this: '08-16

Reply

11 Manish Gupta says:

November 3, 2016 at 6:09 am

Hello Svetlana,

I have dates in 2 columns and in 1 column when highlighted - I can see same value in both
the cell and the formula bar.
case-1
cell - Mon 21-Nov-16 Formula Bar - Mon 21-Nov-16

but in another cell

case-2
Cell - Mon 21-Nov-16 - Formula Bar - 21-11-2016

can you please advice whats the di erence in both and how to use them either as case 1
and case 2?

Also, i am interested to know, as to why if the value is taken under case 1, when we add
formula of Cell (with date say cell A2) =A2+2 doesn't give me Wed 23-11-2016

Please help.

Thanks,

Manish

Reply

12 Mike T says:
November 13, 2016 at 1:11 am

How do you get the following date to change on every Sunday automatically

Reply

13 Sanjib says:
November 19, 2016 at 5:58 am

How to Convert 01.01.2016 to 01/01/2016 date format

Reply

Svetlana Cheusheva says:

November 21, 2016 at 8:42 am

Hello Sanjib,

You can use either the Replace All feature to replace all dots (.) with slashes (/) or Text to
Column wizard. The detailed instructions can be found here: Using Text to Columns to
covert text to date in Excel.

Reply

14 Lisa says:
November 23, 2016 at 6:35 pm

i have a column of dates from a program i use, when trying to sort them they sort
numerically by date, then alpha by month (so all the 1st are together, then the 2nds,
etc....i'd like the actually sorted by date oldest/newest yet i always have to convert them
manually (converting column doesn't change/ x anything as it seems to already know it's a
date...but won't alter it by formula) is there another simpler way? (i am able to use other
columns with the formulas to convert to julian then back to standard date, but this seems
tedious and it really seems tedious with the macros i use....TIA.

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 13/27
4/7/2018 How to change Excel date format and create custom formatting
15 dave says:
November 24, 2016 at 4:19 pm

Hi Svetlana!

Can you help me convert the date from the following to the ending day of that week:
16 Wk 28
16 Wk 29
16 Wk 30

I am trying everything but I can't gure it out! Please help as that is the date that comes in
from a Pivot table that can't be adjusted :(

Thank you!

Reply

16 VIVEK says:
November 25, 2016 at 12:01 pm

I have two cells one is represent date (dd-mm-yyyy) and another is time (hrs:mm:ss) I have
to bring both in one cell and in another cell I have to add 1.30hrs in time to show di erent,
as time change if date changing is possible, please help in this regards

Reply

17 Beka Kukulava says:

January 17, 2017 at 11:06 am

Hello Svetlana

I'm trying to format this: 20490521 into date (2049 year, May 21). When I format it as a long
date format it shows ##################. and wrights me that when dates are
negative or too long it is shown with ########.

How can I solve this?

Thank you in advance.

Reply

Svetlana Cheusheva says:

January 17, 2017 at 12:26 pm

Hello Beka,

Excel displays hash signs when there's not enough room in the cell to show a long date,
in this case you just need to make the cell wider. In your case, Excel may not recognize
the number as a date, and you will have to convert it as explained in How to convert 8-
digit number to date in Excel.

Reply

18 Doroth Oz says:
January 19, 2017 at 3:36 pm

im trying to put conditional formatting for highlighting cells with the formula in Rule of
Conditional formatting, =(TODAY()-$G$2)>7 ,where $G$2 is of date format " 1/18/2017
6:35:45 AM".
But formula is not working, is that it is returning a data format something like this
"1/1/1900 12:47:19 AM".

Reply

19 Khokon says:
January 28, 2017 at 5:01 am

How to ADD below with same format-

1d,4h,30m,0s (which I get from [=INT(E3-D3)&"d,"&HOUR(E3-D3)&"h,"&MINUTE(E3-

D3)&"m,"&SECOND(E3-D3)&"s"]
2d,8h,30m,0s (which I get from [=INT(E3-D3)&"d,"&HOUR(E3-D3)&"h,"&MINUTE(E3-
D3)&"m,"&SECOND(E3-D3)&"s"]

Please help me...

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 14/27
4/7/2018 How to change Excel date format and create custom formatting
20 Nadeem says:
February 1, 2017 at 7:49 pm

If i enter a date in MM/DD/YYYY format it automatically change to DD/MM/YYYY format

within the date of 1 to 12. if the date after 12, it shows correct format in MM/DD/YYYY
format.

My format Cells has the format as MM/DD/YYYY. I need all the date must show in the
format of MM/DD/YYYY.

Note: I dont want to change my regional language settings.

Please tell how to x

Reply

21 XXXX says:
February 8, 2017 at 7:02 am

HI,, IF I ENTER 21/2015-16 I WANT TO CHANGE 21 WILL BE CHANGED LIKE 22,23,24 ONLY
SERIAL NUM WE CHANGE HOW TO CHANGE THAT TYPE PLESE HELP...I NEED SHOW IN THE
FORMAT LIKE 22/2015-16,23/2015-16

Reply

22 Guna says:
February 10, 2017 at 11:10 am

Hi I need the format of this data (6/25/2010 10:59:09 AM IST)

which I downloaded as dd-mmm-yyyy. Please give a solution

Reply

23 Deepak Bhatt says:

February 11, 2017 at 8:25 am

Hello...I would like to change only date from 1/1/17 to 31/1/17....When we drag as per
instruction..... it is displaying like this
1/1/18
1/1/19
1/1/20....

in my case year change instead of date....lease give a solution....

Reply

24 Deepak Bhatt says:

February 11, 2017 at 8:26 am

Hello...I would like to change only date from 1/1/17 to 31/1/17....When we drag as per
instruction..... it is displaying like this
1/1/18
1/1/19
1/1/20....

in my case year change instead of date....please give a solution....

Reply

25 Tapan Bhavsar says:

February 11, 2017 at 10:29 am

how to change a value for example 201001 to read as Jan-2010 in excel?

Reply

26 Yunus says:
February 11, 2017 at 12:19 pm

HI

I want to convert this 22/12/2016 03:22:39 PM to 22-Dec-2016 how do i do it in excel.

Any advise is appreciated

Thanks in advance.
Yunus

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 15/27
4/7/2018 How to change Excel date format and create custom formatting
27 Odile says:
February 17, 2017 at 10:41 pm

Thanks for the article and the numerous informations it provides.

I will just correct one formula :

=DATEVAL("1/1/2015")& TEMPSVAL("6:00 AM")
It will not give the right date (january 1st, 2015) but january, 20th, 3050.

The ampersand has to be changed to a plus sign :

=DATEVAL("1/1/2015")+ TEMPSVAL("6:00 AM")

Reply

28 ronra says:
February 21, 2017 at 9:18 am

HOw do i change/convert the existing date format 21.02.2017 into 21/02/2017 (in the same
cell)?

Reply

29 Frederick says:
March 1, 2017 at 7:39 am

I have consecutive days in a row. Starting form middle January to middle February. When i
change the format of the cells to display "Mon, Tue, Wed... Excel return the 1st day of
February as "Mon" And not "Wed" as the 31st of Jan is a Tuesday.

Reply

30 kishore says:
March 2, 2017 at 1:21 pm

thanks a lot.

Reply

31 Dhar says:
March 6, 2017 at 6:18 am

Hi, may I know how do i convert below date/time to plus 8 hours ahead; and date is
changed accordingly? 05 - date, 03 - month

05-03-2017 4:02:00 AM

Thank you.

Reply

32 SPA says:
March 6, 2017 at 10:09 am

I have a merged document in Excel with a date column format as

'mmm dd, yyyy or 'Sep 07, 2017
and I want to be able to change the entire column format into something recognized in
Excel. So I can sort the dates.

I used to use a plug in called Kutool on Pc - but now I have a Mac and I can't nd a way to
sort this out.

Any advice

Reply

33 SAJID KHAN says:

March 14, 2017 at 1:09 pm

How we can change the date format, in excel if date is written like 03.11.2017. I want to
convert the same entries like 03/11/2017.

Reply

34 Edward says:
March 16, 2017 at 11:47 am

How to convert date in this format

Example:- 15/05/2012 to 15-05-2012

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 16/27
4/7/2018 How to change Excel date format and create custom formatting
Kindly help

Reply

35 Kara says:
March 16, 2017 at 11:46 pm

Hi,

I have the year, for example 1979, but need it to be mm/dd/yyyy and the month and day
just need to be 01/01. Example: 01/01/1979. How can I format the cells? When I do it, it
doesn't recognize the year as the year so the numbers come out all mixed up.

Thanks for your help.

Reply

36 Shawn says:
March 19, 2017 at 11:44 am

Hello!

I would like to use the function =year() and =month() but if i have "1-Feb-17 to 10-Feb-17"
in a single cell, the functions will not be able to work. Is there a way to express that date
range and yet I am able to use those two functions?

Thanks!

Reply

Natalia Sharashova (Ablebits.com Team) says:

April 21, 2017 at 7:43 am

Hello, Shawn,
Do you have a whole column with lots of cells where each contains a couple of dates?
For us to be able to help you better, please, send us a sample workbook with your data
and the result you want to get to support@ablebits.com. Remember to link this article
and your comment in the email.

Reply

37 henry says:
April 1, 2017 at 11:20 am

I have a column with date numbers which do not follow a series e,g 1,2,3,6,8,9 meaning
some dates are missing intentionally and i want to formart the column to have the dates
look like this,1-march-2013 automatically without excel auto corecting the series to eg
1234567 lling the missing dates numbers of dates, is there a way out. please help

Reply

38 Isaac says:
April 6, 2017 at 11:13 pm

I am looking to have a spreadsheet given to users. they will need to be able to input a date
(mm/dd/yyyy format) into A2, and then, without them knowing how to use auto ll, to have
cells b2, c2, d2, and e2 populate with the next 4 days (user puts in 1/2/2017, and b2-e2
auto populates 1/3/2017, 1/4/2017,1/5/2017, and 1/6/2017). I know auto ll function can do
this but I have to assume the end user will not know/remember to do it all the time.

Thank you for any assistance.

Reply

39 Isaac says:
April 6, 2017 at 11:26 pm

Nevermind and thank you, just a simple =a2+1 will solve the issue

Reply

40 Hummayun says:
April 13, 2017 at 12:21 pm

i want to change date format in excel sheet.

from to

05.05.2016 05/05/2016

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 17/27
4/7/2018 How to change Excel date format and create custom formatting
05.01.2016 05-01-2016
02.01.2016 02-Jan-2016
05.02.16 05-Feb-2016
10.02.15 10-Feb-15

how to change it..?

any ideas please share..

Reply

41 Hummayun says:
April 13, 2017 at 12:23 pm

i want to change date format in excel sheet.

From

05.05.2016
05.01.2016
02.01.2016
05.02.16
10.02.15

To

05/05/2016
05-01-2016
02-Jan-2016
05-Feb-2016
10-Feb-15

how to change it..?

any ideas please share..

Reply

42 Peshiyaboy says:
April 14, 2017 at 12:28 pm

Hi Hummayun,

First select your date range then press ctrl+H and replace . with / hit enter.
Now press ctrl+shift+3 that's it.

Reply

Hummayun says:
April 17, 2017 at 9:59 am

Thanks alot Peshiyaboy...

Reply

43 Albert Kitching says:

April 25, 2017 at 7:49 am

Hi,

I want to change 16/03/2016 (text) to a date format: 2016/03/16

Reply

Natalia Sharashova (Ablebits.com Team) says:

May 2, 2017 at 9:09 am

Hi, Albert,

Select your cell with the text, then on Home tab nd a group named Number, and there
choose Short Date. You can change your date format by following this easy tutorial.

Reply

44 prakash says:
April 27, 2017 at 5:38 am

i m unable to change date format form dd.mm.yy to mm/dd/yyy

example --> 21.06.17 i need to change it as 06/21/2017

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 18/27
4/7/2018 How to change Excel date format and create custom formatting
45 Hummayun says:
May 4, 2017 at 5:03 am

a couple of days before my "tonumber" formula sheet working perfectly, now today i open
that sheet and going to work on it all "tonumber" formulas appear "error". my excel sheet
contained a lot of data.please help me how to set "tonumber" formula...........

Thanks in advance

Reply

46 sabeen says:
May 4, 2017 at 5:52 am

JUL-31-2021 how do i change this in excel. Its format is not being changed

Reply

47 kimlong says:
May 5, 2017 at 4:00 am

Excel date 01/04/2014 I want to change date column new to 20140401

Reply

48 Elin says:
May 18, 2017 at 10:34 am

I'm wanting Excel to automatically add the date to EVERY document I print, without me
actually doing it on the print settings. Is there a way to set this up in the default settings
under 'Options'

Thanks in advance

Reply

49 Peter says:
May 24, 2017 at 11:43 am

Hi.
I have date format dd.mm.yy and value 01.01.30. When I change format to dd.mm.yyyy it
gives me 01.01.1930. But I need 01.01.2030. How can I convince Excel to move this
boundary line between year 1900/2000 higher than 01.01.30?

Thanks in advance

Reply

50 Molly says:
May 31, 2017 at 2:51 pm

When I change the date to 01-05-1939 and hit save, then i reopen the save document, and
it didn't save it to the format i had corrected it to be. It is back to 1-2-1939. I re did it and
saved it again and reopened it n it still never saved. Idk why this keeps happening. I even
tried '01-05-1939 and still didn't save when i reopened it.

Reply

51 DILIP RAWAT says:

June 3, 2017 at 7:57 am

MY EXCEL FILE DATA WILL CHANGE WHEN PRESS CTRL+D IM TRYING MORE TIME BUT DATA
NOT CHANGE.PLZ HELP PLZ MY MAIL ID IF POSSIBLE
THIS IS ERROR IN MY FILE-
Unique ID Ledger No. Folio No. Date of Contact. Initial Date of Entry Record Open/Closed
Customer Full Name Customer Address First Line Customer Address Second Line City State
PIN Code Landmark Customer Phone 1 Customer Phone 2 Customer Email ID Customer
DOB Agent Date of Entry Agent Code
Unique ID Ledger No. Folio No. Date of Contact. Initial Date of Entry Record Open/Closed
Customer Full Name Customer Address First Line Customer Address Second Line City State
PIN Code Landmark Customer Phone 1 Customer Phone 2 Customer Email ID Customer
DOB Agent Date of Entry Agent Code

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 19/27
4/7/2018 How to change Excel date format and create custom formatting
52 Joe says:
June 7, 2017 at 11:43 am

I'm looking to create a custom format that starts with 380AEW- after the '-' I need the 2
digit scal year starting in october, 2 digit month, and then a sequential number. for
example 380AEW-170516, the next could be 380AEW-170617 depending on the month. Any
tips would be appreciated.

Thanks,
Joe

Reply

53 Bella says:
June 14, 2017 at 12:50 pm

Hello,
i need to sort out some data by day of the week, i did a custom change on the date column
e.g from "01/10/16" to "Saturday", now how can i get the date in the formula bar to re ect
whats in the sheet below, because it still "01/10/16"??

Reply

54 Undra says:
June 21, 2017 at 9:07 am

Hello, Svetlana.

Could you please help to change date format from 2016.12.22 12.35 to one in which the
time is removed and becomes simply 22.12.16.

Thank you!

Reply

Natalia Sharashova (Ablebits.com Team) says:

June 21, 2017 at 2:03 pm

Hello, Undra,

rst of all, make sure to enter the date in the default format, which is: 12/22/2016 12:35

Then, select the cell with this date, press Ctrl+1 to open the Format Cells window and
switch to the Custom tab. There, in the Type eld, enter the following: yyyy.mm.dd
and click OK

Voilà :)

Reply

55 srinivas says:
June 28, 2017 at 11:43 am

Madam,
If already we have a some of Date format and lled in lot of coloumns then how can we
convert another date format like Ex: 28.05.2015 it already done by lot of coloumns now we
want 28-05-2015 this format. what can i do.

Reply

Natalia Sharashova (Ablebits.com Team) says:

June 29, 2017 at 10:01 am

Hello, Srinivas,

you just need to select the cells with the data, press ctrl+1, go to Custom tab and set the
following data format dd-mm-yyyy
Hope it helps!

Reply

56 Swann says:
July 7, 2017 at 5:12 pm

Hello,

The problem I’m having is my date column was changed to integers. How do I turn my
integers back to dates? I tried all your steps and tips and still no change. But when you
click on the cell the integer changes to the date and when I click o the cell the date turns

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 20/27
4/7/2018 How to change Excel date format and create custom formatting
back to an integer. Help please! Also, my computer was upgraded to Microsoft 365.

Reply

57 KOK says:
July 13, 2017 at 9:16 am

it solve my problem.thanks.

Reply

58 Praveen Kumar Singh says:

July 28, 2017 at 3:12 am

Hi

if i want to do some change in excel sheet and also want that whatever the last time saved
time and date was there it should remain the same. is it possible.
eg: some has created excel sheet yesterday and while validating i did some mistake and
update wrong number in some cell and in next day if i want to change the number again
is it possible that i can change the number without any changes in current date and time
if i save today??? please help me out in this if possible.

Reply

59 Balamurugan says:
July 30, 2017 at 5:47 am

Hi
How to convert date from 1/4/2017 to 1-april-2017 in excel

Reply

60 Hakim says:
August 13, 2017 at 3:24 pm

Hi there,

Previously my 2007 excel showed up Agt-17, and now after upgrading to 2016 it became
Agu-17. Since I refresh a lot from my sta s les and they are still using o ce 2007, I can't
get the values from the month of August as mine will be showing Agu-17 and theirs are
still in Agt-17.

Can anyone advice me on how to change the format of the month show up?

Thanks

Reply

61 Igor Kennaway says:

August 18, 2017 at 1:15 pm

I wish to know the SIMPLEST method to change date format from 8/18/2017 to 18/8/2017.
The former is confusing since in England it would suggest that the date is the 8th of the
18th month, which is clearly nonsense. I assume this ridiculous format must be imported
from the USA and as such, is of no use to me, leading to as much confusion as the present
USA President. Please advise.

Reply

62 santosh says:
September 1, 2017 at 3:30 pm

when i enter 20/12/2008 as date in excel then it appers as 391204 so what can i do

Reply

Natalia Sharashova (Ablebits.com Team) says:

September 4, 2017 at 2:17 pm

Hello,

you need to change number format, since it is set to show you numbers rather than
dates.

Please take a closer look at this point of the article above to see how it's done.

Reply

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 21/27
4/7/2018 How to change Excel date format and create custom formatting
63 PRASHANT KUMAR says:
September 11, 2017 at 10:20 am

HOW CAN I CHANGE THE FORMAT OF DATE IN EXCEL :

25-01-2017 TO 01-25-2017
PLEASE HELP ME IMMEDIATELY

Reply

64 suhas gade says:

September 28, 2017 at 2:10 pm

I want this Date (17/03/1988) as 17031988 please give me solution

Reply

65 mannec says:
October 7, 2017 at 7:31 pm

I set date format to mmm-yy.

I entered 9/99. Excel displayed Sep-99 as I expected.
I entered 6/06. Excel displayed Jun-17. It did the same for all years after 2000.
I nally gured out that I need to enter 6/2006 (not 6/06) for years after 2000 to get the
correct display: Jun-06. Is Excel stuck in the last century/millenium? Is there a way around
this -- the need to enter the full 4-digit year?

Reply

Natalia Sharashova (Ablebits.com Team) says:

October 11, 2017 at 8:32 am

Hello,

I regret to tell you but this is a standard Excel behavior. For it to recognize whether you
want to enter 2006 rather than June or 1906, you need to input all four digits of the year.

Reply

66 MichaelNi says:
October 12, 2017 at 10:31 am

How to convert a text string 10-Oct-2017 to a number in excel?

Reply

Natalia Sharashova (Ablebits.com Team) says:

October 13, 2017 at 8:19 am

Hello, Michael,

There are a few ways you can follow. This tutorial explains everything in detail, please
have a look :)

Reply

67 Jacob says:
November 1, 2017 at 2:03 pm

Hi,

If i have a number such as 201709 in a cell, is it possible to format the same cell so excel
knows it is a date (september 2017) and not a number?

Naturally the date 2452/04/3 now shows.

Thank you

Reply

68 Toni C says:
November 2, 2017 at 3:14 pm

I am trying to convert a date from 20060911 (YYYYMMDD) to 09112006 (DDMMYYYY) I have

tried to use the format but only received ########### is there a formula to do this?

Reply

Gennady Terekhov (Ablebits.com Team) says:

November 3, 2017 at 2:41 pm

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 22/27
4/7/2018 How to change Excel date format and create custom formatting
Hello, Toni,

Suppose, your original value «20060911» is in cell A1. If you place this formula
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"ddmmyyyy") in cell B1, for example, you’ll
get the result you need.

Hope this will work for you.

Reply

69 Esther says:
November 14, 2017 at 1:50 am

I would like to convert dates into month then by week.

For example 01/05/2017 to 07/05/2017 would equal May week 1 2017 and 22/05/2017 to
31/05/2017 would equal May week 4 2017 then be able to ltered them to show in
week/month order not A-Z
Is this possible?
Thank you

Reply

Gennady Terekhov (Ablebits.com Team) says:

December 13, 2017 at 8:57 am

Hello, Esther,

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 con dential 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.

Reply

70 Karen Pa ey says:
November 21, 2017 at 6:03 pm

I have dates in Excel which are entered either as mm/yy or dd/mm/yy; they display as
mmm-yy - which is correct. However, the date is used in a calculation and I want it to be
read as the last day of the month (end of month) - is there a way to format the cells to
ensure that the date is referred to as end of month?
Thanks in advance.

Reply

71 Martin says:
November 23, 2017 at 6:28 am

When formatting my date it shows as 20/MM/2017 when it should be 20/11/2017.

The month displays as MM instead of the actual month.
With the cursor in the cell, the date in the function bar at the top shows correctly as
20/11/2017 but in the cell itself as 20/MM/2017.
Changing the format to e.g. 20 November 2017 will show 20 MMMM 2017 in the cell.

Reply

72 dee says:
November 23, 2017 at 5:04 pm

help me subtract the dates below..i tried but i got -0.11

13/05/2017 10:14:57 AM
13/05/2017 7:40:04 AM

thanks

Reply

Gennady Terekhov (Ablebits.com Team) says:

January 9, 2018 at 3:05 pm

Hello,

You can use a formula like this:

=TRUNC(IF(A1>A2,A1-A2,A2-A1))&" days "&TEXT(IF(A1>A2,A1-A2,A2-A1)-

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 23/27
4/7/2018 How to change Excel date format and create custom formatting
TRUNC(IF(A1>A2,A1-A2,A2-A1)),"h:mm:ss")

where cell A1 is “13/05/2017 10:14:57 AM”, cell A2 is “13/05/2017 7:40:04 AM”

Hope it will help you.

Reply

73 Dr Saju S V says:
December 3, 2017 at 10:44 am

I've entered date in dd.mm.yyyy format. But excel is not detecting as date. How this can be
solved?

Reply

74 John says:
January 2, 2018 at 3:20 am

Hi, I have a problem not with formatting, but with entering dates.
If I enter 2/1, it appears as 1 Feb not as 2 Jan which I want. Cell is formatted correctly (I
think) as d-mmm (or 14-Mar from the date list) but the data entry I suspect is wrong.
Computer (Mac) is correctly set to English Australian and I think only Excel has this
problem. It only started after a recent reload. Something simple I have overlooked?

Reply

75 Stijn Haelewyck says:

January 5, 2018 at 9:49 am

Hi,

I have an Excel table where the le name is a (weird)date format.

For example in plain text it says: 1805.01

This is actually a date format(yymm.dd).

Is there a way to convert this to a normal date format so I can lter the column by date
(e.g. dd/mm/yyyy or 01/05/2018)?

Many thanks!

Stijn

Reply

76 Sumukh says:
January 10, 2018 at 1:35 pm

The problem i'm facing is all the dates are in American Format i.e MM/DD/YYYY but i want
in Indian Format i.e DD/MM/YYYY i've thousands of entry i changed the region to India but
how do i change the format to all the dates at Once, Do I've to do it Manually or there is a
formula which will automatically change it from MM to DD and DD to MM?

Reply

77 Maggie Jansen van Rensburg says:

January 18, 2018 at 8:30 am

I'm trying to change a date from 02/01/2018 to 2018/01/02, when tipe it in it changes to
2018-01-02, the format I want isn't even on the format list. Everything on my region setting
shows correct. This must be some default setting.
Thanks

Reply

78 jasper says:
January 19, 2018 at 10:31 am

Hello,

How do i take out the dots using the formula. the below is date example

17.04.2001
01.07.2000
01.04.2002
15.01.2004
01.03.2004

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 24/27
4/7/2018 How to change Excel date format and create custom formatting
Reply

79 Mark says:
January 19, 2018 at 4:04 pm

'How to change date format in Excel': just followed these instructions to the letter, doesn't
work, and checked, the dates are not in text format. The only way I can get the dates to
change is by double clicking one box at a time then coming out of it, which is a rather slow
way of changing a whole list of dates.

Reply

80 Tushar says:
February 2, 2018 at 8:35 am

I want to change the date written in '02-02-18 format

but i want to write 02-Feb-18 format

Reply

Ekaterina Bespalaya (Ablebits.com Team) says:

February 5, 2018 at 2:43 pm

Hello, Tushar,

It looks like your date is formatted as text right now. First you need to convert it to the
date format. You can do this by using the standard Excel Text to Columns feature. Just
select the range with your dates, go to Data -> Data Tools -> Text to Columns, choose the
“Delimited” data type on step 1 of the Wizard and click Finish. Your data will get the
default date format, 2/2/2018.

After that press Ctrl + 1 and specify the necessary Date format, 02-Feb-18, for your data
in the Format Cells dialog window. Click OK.

Hope this information will be helpful for you.

Reply

81 Jay says:
February 3, 2018 at 10:30 pm

I'm saving one worksheet in a large workbook as CSV and nding the CSV le does not
respect the change in default date format in that the step where I copy the worksheet to a
temp sheet ends up using Excel's original default format therein and thus in the CSV. If I
highlight the sheet by hand and save in this manner then "WISIWIG."

Reply

82 Lisa says:
February 18, 2018 at 12:53 pm

Hello,

Could you please guide me in auto- lling the dates in the format as mm.dd.yyyy ?

When I right click and drag the dates for ll series, the date unit is disabled and only the
year is auto- lled. Is it because of the format (mm.dd.yyyy).? How could I auto ll the days.

Thank You.

Reply

83 Andrew says:
February 19, 2018 at 5:39 am

Hi,

I have a column of durations formatted as [h]:mm:ss where I want to hide 00:00:00 values.
Can I create a custom format to do this?

Thanks,

Reply

84 Eugene Brennan says:

February 19, 2018 at 1:01 pm

For some reason, Excel isn't using the regional formatting for dates that I have set in
control panel. So instead of DD/MM/YY, I'm getting MM/DD/YY. The version of Excel is old,

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 25/27
4/7/2018 How to change Excel date format and create custom formatting
97 I think so maybe it can only do American format dates.

Reply

85 Angad says:
February 21, 2018 at 11:30 pm

Hi
How can i change format to bilingual. in Canada we use date code on products so it can be
read as English/French. See below for codes for months. example. February 21, 2018 can
be written as 18 FE 21. Is it possible to do this in excel please?
JA
FE
MR
AL
MA
JN
JL
AU
SE
OC
NO
DE

Reply

86 LUZ says:
March 8, 2018 at 4:05 pm

Hello!

I have this date in my sheet:

20122017
14022018
2022018

I need yyyymmdd, but then I need to move to access.

Help

thanks

Reply

87 Kay says:
March 16, 2018 at 3:01 am

I need to have the dates for the year auto populate, but I want to have 2 letters in front.
For example; AB 15-3-18
Please help!

Reply

88 manimaran says:
March 30, 2018 at 2:16 pm

how to nd the di erence between'01-MAR-18'at the 1918/03/01 Can you please provide
me a solution for this.correct pls 1-mar-2018

Reply

← Older Comments

Post a comment
Name Name

name@example.com E-mail (not published)

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 26/27
4/7/2018 How to change Excel date format and create custom formatting

Send

Unfortunately, due to the volume of comments received we cannot guarantee that we will
be able to give you a timely response. When posting a question, please be very clear and
concise. We thank you for understanding!

Copyright © 2002 - 2018 Ablebits.com. All rights reserved.   Terms of use   Contact us
Microsoft and the O ce logo are trademarks or registered trademarks of Microsoft Corporation in the United
States and/or other countries.

https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/ 27/27