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!

 

[toddmkl]

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

cute meaningful baby boy names



While I was able to do this with everyone's help I have been told it won't work for what we are trying to do. Column K comes from a data source that includes a timestamp. The last three columns are the DAY|MONTH|YEAR columns you helped me with on here via the formatting.

However, since our WHERE clause in SQL points to column K and that Where Clause is for Dates >2018/01/01 it's not displaying the Slicers correctly as you can see. My co-worker said I need to find a way to get rid of the timestamp from Column K and make the last three columns display the correct information without using the format feature or the Slicers will not work because of that timestamp.

Thanks

 

[steve the fish]

Are you actually using a formula to get the day, month, year to display or are you using formatting? I suspect the latter. You need to use the formulas to get the day, month, year in the cells.

 

[toddmkl]

Are you actually using a formula to get the day, month, year to display or are you using formatting? I suspect the latter. You need to use the formulas to get the day, month, year in the cells.

I was using formatting but my co-worker advised me that that messes up the backend data. So, I needed to write a formula. So, I ended up using the =INT(SHIPDATE) formula and then doing my =DAY

([@SHIPDATE]) , =MONTH

([@SHIPDATE]) and =YEAR

([@SHIPDATE]) formulas using the INT formulas column as the (@SHIPDATE) to get the data I wanted.

Thanks Steve the Fish!


​

​

​

 

[steve the fish]

Day month and year formulas will not be affected by a date that has a time component so the int part is unnecessary. You will still get the same result if it's used or not.