deleting out seconds from a date

[stephme55]

Hello. I have dates with 1/1/2016 12:30:00 PM for example. I wanted to match them to dates (1/1/2016) but the dates don't match because the minutes don't match (I believe that's why they don't match). How can I delete the minutes off?

ALSO, this format is already in powerpivot. Can I change it in there (I don't think changing just the format will work). Thanks.

[FDibbins]

Depending on what you are doing with the dates, you may not need to strip the time out...what are you doing?

You could use a helper column with this, to remove the time...
=INT(cell-ref)

[stephme55]

I'm trying to create group by dates in a powerpivot. The first table has the dates formatted with minutes, the linked table that I created does not. I didn't understand cell-ref? Thanks.

[XLPadawan]

Assuming 1/1/2016 12:30:00 PM is in A1:

=TEXT(A1,"m/d/y hh") ========> 1/1/16 12
=TEXT(A1,"m/dd/yyyy hh") =====> 1/01/2016 12
=TEXT(A1,"mm/yyyy") ========> 01/2016

But now your dates are text and will not match to dates that are actually date-formatted numbers. Easiest thing to do would be to format both as TEXT (using the same formatting mask) and THEN comparing the text strings.

[FDibbins]

I'm trying to create group by dates in a powerpivot. The first table has the dates formatted with minutes, the linked table that I created does not. I didn't understand cell-ref? Thanks.

I am suggesting that in your data table, you create a helper column and use the INT() function top strip out the time. I used Cell-Ref to indicate the cell that would contain the data.
If the data starts in A2, then =INT(A2)
If the data starts in B3, then =INT(B3)
etc

You wiould then use that column in your PT instead of the date/time column

[FDibbins]

Assuming 1/1/2016 12:30:00 PM is in A1:

=TEXT(A1,"m/d/y hh") ========> 1/1/16 12
=TEXT(A1,"m/dd/yyyy hh") =====> 1/01/2016 12
=TEXT(A1,"mm/yyyy") ========> 01/2016

But now your dates are text and will not match to dates that are actually date-formatted numbers. Easiest thing to do would be to format both as TEXT (using the same formatting mask) and THEN comparing the text strings.

It is almost always better to leave dates as dates, that way you can use 1 of the many date functions built into excel. If you convert them, you may need to convert them back afterwards, a potentially unnecessary extra step