• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Split Time with Date Value

mf1

mf1

Member
Hi,
I need some help please.

I require a formula to remove the time, then have the date appear as a date, not text.

i.e.
June 2, 2015 12:00 pm

Thanks,

mf1
 
hey
try this
For a date/time in A1
Example: A1: 5/19/2006 2:07:25 PM

B1: =INT(A1)
Format that cell as a date

C1: =MOD(A1,1)
Format that cell as time

see if this helps u...
 
u can try this too

Ctrl + semicolon will insert the date into a cell
Crtl + Shift + colon will insert the time into a cell
To split an existing cell between date and time use....
Data > TextToColumns > Fixed > and insert a column break between the two...

Regards,
Jaya
 
sorry, I should have been clearer, at the moment A1 = June 2, 2015 12:00 pm.

I want A2 to = Tuesday, 2 June 2015 (or whatever date format I select)
 
Hi,

Why not just format the col A with custom format [$-F800]dddd, mmmm dd, yyyy

or if only date req then this also

--LEFT(A2,5)
 
Last edited:
Hi Deepak,

Yes, I only only require the date and tried your formula =--LEFT(A2,5) and it returns a #VALUE! error. Not sure what I'm doing wrong? (The data source is an excel data dump from a website and the cell is in general format, not date)

Also, I will have various dates returning that the formula will need to calculate.
June 2, 2015 12:00 pm
September 20, 2015 9:00 pm
May 15, 2015 3:00 pm


Cheers,
mf1
 
Hi Everyone,

After much trial & error, I got it to work with this formula.

=IFERROR(DATEVALUE(REPLACE(TRIM(LEFT(S9,15)),1,FIND(" ",S9)-1,LEFT(S9,FIND(" ",S9)-3))),"")

Thanks for your help:)
 
You must log in or register to reply here.
Back
Top