Hi everyone,
I have a column which has date/time stamp in following manner
6/15/2015 12:35:12 PM
I need to keep the date & remove time from the cells. I have been google & tried all sort of formatting with Month, Day & Year formula but it is giving me a #Value! error.
Thanks in advance for any help.
Regards,
Vikas
=MOD(A1,1) and format as time??
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
assume you are using DD-MM-YYYY
you need to convert the date to your own convention
easiest way i found is just text to column split it using space as delimiter
while there change the date format to your country specific format
Last edited by humdingaling; 06-15-2015 at 04:02 AM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
=int(a1)
try the above formula
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Ha Ha. I misread this completely. I would swear that I read "keep the time and lose the date. Never mind, nflsales is spot on!!
int would work if your date format is MM-DD-YYYY
it doesn't work in Australia
i thought India uses DD-MM-YYYY as well, whereby month 15 would give error?
May be ,the format of the cell where the Text"6/15/2015 12:35:12 PM" appears is formatted into custom field.Try changing the format to "short date or long date" in the format cell in home page.
Assuming that the source cell is text and not a date I tried this and was able to get a date value.
Please Login or Register to view this content.
Sorry I forgot to add a text() function to the first MID() to guarantee that the day is 2 characters long. Being in the UK my date format is DD/MM/YYYY and this works to that. If this does not give the desired result swap the first Mid() with the Left() function as below.
DD/MM/YYYY
Please Login or Register to view this content.
MM/DD/YYYY
I hope this isn't too confusing.Please Login or Register to view this content.
This is a much simpler way of converting dates
http://projectwoman.com/2011/03/exce...dy-to-dmy.html
and because you are already using text to column you can split with either fixed width or space delimiter while your there
if you really dont want to delete the excess columns you can skip import while your doing text to column
you dont have to remember the formula and copy it from other sheets etc
INT will work no matter what the date format is, provided you have 'real' dates.
If posting code please use code tags, see here.
This is an old thread, and my first post, but I recently found out about the =Int() function and well, combined with this VBA its so sleek it hurts. Inserts column and formula then removes old data. Check your headers and columns for special preferences, but I'm a noob and I was even able to tweak this to my special needs. HAVE FUN!
Dim LR As Long
Columns("j:j").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("j1") = "Date of Adjustment"
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("j2:j" & LR)
.FormulaR1C1 = "=INT(RC[-1])"
.NumberFormat = "mm/dd/yyyy"
.Value = .Value
End With
Columns("i:i").Delete Shift:=xlToLeft
If the date and time are in A1
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks