System Dates.....

Joined
Jun 22, 2015
Messages
28
I am using a system that when downloading data displays dates as follows:

Mar 17, 2015 05:35pm
Apr 1, 2015 12:07pm

When I try and reformat these as standard dates dd/mm/yy nothing happens and I am trying to calculate the number of hours or days between two of these dates.

All help appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hope this helps.

A1=Mar 17, 2015 05:35pm
B1=TEXT(DATEVALUE(LEFT(A1,FIND("2015",A1)+3)),"dd/mm/yy")
or B1=DATEVALUE(LEFT(A2,FIND("2015",A2)+3)) then change format.
 
Upvote 0
Takae,

Thanks for the response. I have tried both formulas and they both return a #Value! error.

I'm not sure how to upload a sample spreadsheet to this site so that you can get a better representation of what I am trying to achieve.
 
Upvote 0
Where is "Mar 17, 2015 05:35pm" in?
A1? A2?
And "Mar 17, 2015 05:35pm" is actual data?
If the data doesn't have "2015", the formulra will be #value! too.
 
Last edited:
Upvote 0
The data is in cell H3, but I amended your formula accordingly:

=TEXT(DATEVALUE(LEFT(H3,FIND("2015",H3)+3)),"dd/mm/yy")

=DATEVALUE(LEFT(H3,FIND("2015",H3)+3)) then change format.

As for the data,
"Mar 17, 2015 05:35pm" it is data in a cell and formatted as general as standard. The data definitely has the year (2015) within it.

Hope this helps.


 
Upvote 0
Correct fomura...
Press F9?
I see, it is languege diffirence.
I wil check.
 
Last edited:
Upvote 0
It was correct...
My test was like this.
Mar 17, 2015 05:35pm </SPAN> 17/03/15</SPAN>
Mar 17, 2015 05:35pm</SPAN>17-Mar-15</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
 
Upvote 0
Let's check step by step until other specialist answers.

What are the returns?
H3=Mar 17, 2015 05:35pm I3=FIND("2015",H3)+3
H4=Mar 17, 2015 05:35pm I4=LEFT(H4,FIND("2015",H4)+3)
H5=Mar 17, 2015 05:35pm I5=DATEVALUE(LEFT(H5,FIND("2015",H5)+3))
 
Upvote 0
Please try this code.
Returns is ColumnI.

Code:
Sub datev()Dim Target, buf As Long, str As String
Dim LastR As Long


LastR = Cells(Rows.Count, 8).End(xlUp).Row


For Each Target In Range(Range("H3"), Cells(LastR, 8))
    buf = InStr(Target, "2015") + 3
    Target.Offset(0, 1) = DateValue(Left(Target, buf))
    
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top