Strip time from date

Cells containing dates I formated as d-m-yyyy and the cells are showing dates like "19-2-2014". However if I want to use the dates in a pivot table and want to select a range I also see the time value forcing me to select too many entries. In the pivot the date showes like this "19-2-2014 15:51:09"

How can I strip the time (and if possible the day) from the date field? Can this be done with a formula or do I need something in VBA?

Try using =INT(a1) format as date

Date is just a number that represents how many days have passed since 1/1/1900, and time is a decimal value. 06:00 am is 0.25, 12 noon is 0.5 etc

Can't you group by day?

well, crap. that beats what I've been doing:
=DATEVALUE(MONTH(A4)&"-"&DAY(A4)&"-"&YEAR(A4))
It works but obviously not as clean/quick lol

Originally Posted by Jonmo1

You can use the INT function to get just the date..

=INT(A1)


And to get the time only
=MOD(A1,1)

Thanx for this explanation, this works for me.

Originally Posted by FDibbins

Try using =INT(a1) format as date

Date is just a number that represents how many days have passed since 1/1/1900, and time is a decimal value. 06:00 am is 0.25, 12 noon is 0.5 etc

Thanx for the explanation, this works for me.

Happy to help and thanks for the feedback