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

[toddmkl]

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!

 

[Gerald Higgins]

Hi, welcome to the board.

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

 

[steve the fish]

That is 27. Its just formatted to date. Format it to general or number.

 

[toddmkl]

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>

 

[toddmkl]

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.

 

[Gerald Higgins]

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.

 

[toddmkl]

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.

​

 

[toddmkl]

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.

 

[steve the fish]

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.

 

[toddmkl]

Thanks to Gerald and Steve the FISH I figured this out !! Thanks everyone!