Excel - Format a date the way you want - Custom date formats

Excel has plenty of date formats you can get from Format cells (CTRL + 1) is the keyboard shortcut. If you want, you can create your own custom date format. This short Excel tutorial will look at Long Date, Short Date, and several custom date formats.

Get a list of date formats:
****
Select the cells you want to format.
Press CTRL+1.
In the Format Cells box, click the Number tab.
In the Category list, click Date.
Pick Date in the Category list
Under Type, pick a date format. Your format will preview in the Sample box with the first date in your data.

Chapters:
0:00 Intro
1:33 Date formats
2:20 Create a custom format date
5:00 Sorting custom dates
5:53 CTRL + ; current date
6:30 Dates are numbers

Create a custom date format
****
Select the cells you want to format.
Press CTRL+1.
In the Format Cells box, click the Number tab.
In the Category list, click Date, and then choose a date format you want in Type. You can adjust this format in the last step below.
Pick Date in the Category list
Go back to the Category list, and choose Custom. Under Type, you’ll see the format code for the date format you selected in the previous step. The built-in date format can’t be changed, so don’t worry about messing it up. The changes you make will only apply to the custom format you’re creating.
In the Type box, make the changes you want using code from the table below.

Note: if you get #####, you need to AutoFit your columns. Get between the two columns and double click to AutoFit. You can also manually drag to the right to resize the column.

#msexcel #chrismenard #chrismenardexcel #chrismenardtraining

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[alanarana]

Chris, you rock!! You are to the point and quick. 5-STARS

[keeleyb]

Always learn something from your videos.

[dianamcmillan]

Thank you this was just what I needed to get the short date I wanted

[Dwavard]

Great job! I can now get a day Infront of my dates :-)

[onealkeisha]

Thos was an awesom tutorial. Thank you

[yawoteng]

Thank You for the video, are you able to do a custom format for dates where the month is in caps?

[AjayMenonPHOTOARTIST]

Very useful. One question though. I want the default date when I use the Control Colon option to be in the following format.
dddd, MMMM d, yyyy
Sunday, January 8, 2023
How do I do this?

[xyuv]

such a great video, but I have a question can be write the months and date in caps

[bluefishaddicted]

thank you for the video.
I have a column with random date started in the midle of the week and I need weekend dys to be colored in different color automatically, to be easily indicated after that.
Could you advise me how I could do, please?

[SueFerreira]

Thanks for your videos and clear explanations..
Why is the US illogical by writing dates as Month/Day/Year? Day/Month/Year is so much more sensible.
It's the same illogicality of still using the Imperial System rather than the System Internationale.

[phknutson]

Chris - I now this is beyond date formatting.... If I have a column with "JUN 11-12' how can I divide that up to 2 columns with '6/11' and '6/12' ?

[BentleyLittleman]

Is it necessary to format a date to year only? I am certainly experienced with excel; however, I have never been able to format a number to be yyyy only (2024 or 1976, for example).

[talkingenergies]

I pasted a number of dates. For instance 1988-3-24. Plus maybe 10 down with different ones. I want this to be 3/24/1988. Different cell. Same for others. I have done this all under cell formatting. It will not let me change the format. Everything stays exactly the same. What can I do to make this work.

[j.rjunior]

Hello Chris, I'm wondering is there a way to do custom formatting but to get the last day of the month.

[Glitterinez]

Hi! i have a problem. I used to be able to type dates like this
6 11 2023

and Excel would read it as a date and when i click long or short date it follows format.
now it doesnt. I had to get my laptop reformatted and got the new windows and offices so im still figuring out all the issues in regards to my preferences.

it only reads 06/11/2023 as dates and this just annoys me because it wasnt a problem before.

thank you so much

[jaiciek]

Ages ago, when Excel allowed you to actually customize, I was able to set the date to auto add in a specific year. Meaning, I want the same year to show up in the column where I'm adding dates. I have nearly 1, 000 entries to make, on each sheet, so not having to type the year on that sheet would be great! I used to be able to customize with mm/dd/2022. Then type in 03/21, but it would show 03/21/2022. The next sheet I could customize it to whatever year I was working on that page mm/dd/2023. Unfortunately, it's not allowing me to do any of that now. :(
Is there a way to customize the date in such a manner?

[justinheiss]

Is there a way to change the data to get only one date per month on dates?

[ayanazar]

Hi, I have one question, I want a date format with only the and the year, how can I do that?

[tazucky]

Hi Chris, I have a coulum of merged cells with a custom format date, which i need to stay centre of the column. I have the day name on top, I've then used ALt0010 to drop the rest of the date so it displays below the named day, however this then doubles the cell's width. Is there anyway I can stop the cell width doubling? Thanks Mark.

[thatomorwe]

Hi Chris, how do i pre set date to be the same format no matter how i type it in?