Extract date

[KenHigg]

Does anyone know how to get the date part out of this date/time cell:

12/15/2005 7:17:27 PM

 

[HaHoBe]

Hi, Ken,

have you tried

Debug.Print CDate(CLng(ActiveCell.Value))

or

Debug.Print CDate(Int(#12/15/2005 7:17:27 PM#))

Works for me (with a different windows system Date And Time system).

Ciao,
Holger

 

[shades]

If you want to use a formula approach (be sure that the cell has no extra spaces at the beginning), suppose that is in cell B3, then in cell C3, put this:

=DATE(YEAR(B3),MONTH(B3),DAY(B3))
________
R60/2

 

[KenHigg]

I'll give those a look - Thanks.

 

[ideefixe]

Format(ActiveCell.Value,"dd/mm/yyyy") will also do the trick.

But be careful with dates in Excel. It does a bit of sneaky formatting all by itself, as I found out when I saw a date of birth somewhere in 2020. The above approach has the advantage of keeping the date in strings.

 

[cygnette]

Had the same problem myself. Try this. It worked for me.

TO CONVERT 9/1/2011 9:52:00 AM TO "Sep 1" or the like follow these steps.

If your data is in B6, use formula below:

=TEXT(B6,"mmm d") or
=TEXT(B6,"mm/d/yyyy") depending on your preferred date format.

Once you get the "Sep 1" output, Copy and Paste on the same cell using ALT+E+S+V

Hope this helps

 

[NBVC]

How about just?

=INT(A1)

where A1 contains the date/time you posted. You can then format to any date format you wish.

ooops... just noticed this is a 2006 thread!

 

[Brianwarnock]

ooops... just noticed this is a 2006 thread!

Why does that matter, surely it works and date handling hasn't changed has it?

Brian

 

[NBVC]

It still works, yes, but I figure the OP is long gone and doesn't need this anymore... although, it might be useful for others...

 

[cygnette]

Yup, might help someone else who needs it. Like myself. Found this forum when I desperately needed the answer. Might save someone else the frustration.

I have a little problem with the formatting thing. Cause if you just format, the time is still there, it's just the display that's changed. If you use your date on a pivot table, the date will still appear too long (Ex. 9/1/2011 9:24:00 AM) despite the formatting.

Not sure if there is any other way other than using TEXT. Maybe do some tweaks on the pivot table format instead to show only the date in a "Sept 1" format.

 

[NBVC]

Using INT(A1) will cut off the time portion... but if you format the cell with date/time, then you will see a "time" portion of "0:00" or similar...

The TEXT() function will give you a text string and so will not really be a date... and converting it to a date with say +0 to coerce it will make it numeric (date) again, but the formatting will then give same issue as above...

 

[cygnette]

I see. Will try this out with my data.

Thanks for the additional info! That is very helpful of you.