Need Formula to change M/dd/yyyy 0:00 to three separate columns, M|D|Y

toddmkl

New Member
Joined
Feb 21, 2019
Messages
8
Hello,

I have a Column K named 'SHIPDATE' and the first date starts in K2 and is in the format 9/27/2017 0:00.

I need to make three separate columns from K2 named MONTH|DAY|YEAR

So for Day I used this formula:

=DAY([@SHIPDATE])

But the output for Day is

1/27/1900 0:00

I'm trying to get it to output 27 instead of 1/27/1900. I'm not sure how to do this.

Thanks!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
That is 27. Its just formatted to date. Format it to general or number.
 
Upvote 0
Hi, welcome to the board.

Keep your current formula but just change the format, to CUSTOM, then DD.


Thanks Gerald. I'm using Office 365 and I don't have a CUSTOM format unless I'm just misunderstanding.

Here you see Column K and when I tried setting column P to
=DAY([@SHIPDATE]) I get 1/27/1900 0:00 instead of just 27 I also tried typing in
=CUSTOM
([@SHIPDATE]) but Excel didn't like that. Thanks
<strike></strike>

 
Upvote 0
The Timestamp is giving me issues and when I do the traditional formatting to remove the timestamp Column P still wants to include the timestamp.
 
Upvote 0
This is done by applying cell formating, not changing the formula.

Select a cell in column P.
Open the "Cell Format" dialog box.
There are lots of ways of doing this.
One way is to right click on the cell, and on the menu that pops up, choose the option to Format Cells.
Then choose the Number tab.
Then for my solution, choose CUSTOM, and then enter DD in the TYPE box.
OR, still on the Number tab, for Steve the Fish's solution, choose NUMBER.
 
Upvote 0
Hi, welcome to the board.

Keep your current formula but just change the format, to CUSTOM, then DD.

Gerald. I see what you mean now. I format the new column to be CUSTOM and set the text to DD. THen I use my same
=DAY([@SHIPDATE]) formula and it still outputs 1/27/1900 0:00 instead of 27.

 
Upvote 0
This is done by applying cell formating, not changing the formula.

Select a cell in column P.
Open the "Cell Format" dialog box.
There are lots of ways of doing this.
One way is to right click on the cell, and on the menu that pops up, choose the option to Format Cells.
Then choose the Number tab.
Then for my solution, choose CUSTOM, and then enter DD in the TYPE box.
OR, still on the Number tab, for Steve the Fish's solution, choose NUMBER.

Okay, I got what you are saying there and tried that. It flashes 27 just for a second and then it still brings up the 1/27/1900 0:00 . I think the timestamp in the SHIPDATE column is causing me issues.
 
Upvote 0
You cant use custom formatting to DD if you have already converted the date with the DAY formula. Well actually DD will work but MM isnt going to. You just need to use general or number if you have a formula. If you just have the date itself then you can use the custom formatting DD. Enter the formula in the table then right click the column and 'Format cells' then general.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top