=$F$3-(DATE(MID(E7,FIND("/",E7,FIND("/",E7)+1)+1,4),LEFT(E7,FIND("/",E7)-1),MID(E7,FIND("/",E7)+1,FIND("/",E7,FIND("/",E7)+1)-FIND("/",E7)-1))+TIMEVALUE(MID(E7,FIND(" ",E7)+1,20)))
I think you would need to choose MDY rather than DMY - this is telling Excel how the source data is arranged.Select the range starting in E7 ( alternatively H7)
Data - Text to columns - Check " Delimited" - Next - Check space - Next - Select the first column and check Date DMY - Select the other columns one at a time and check " do not import" - Finish
Now you have clean dates
DMY yielded questionable results for me in the UK, but MDY returned 100% correct interpretation (Column A):I use the regional settings for Belgium. BTA I usually try the MDY version and if it doesn't work the DMY ( which did it for me in this case)
Sub blah()
'This works on the selection (if you have Power Query), it doesn't need to make a table of the source data and it doesn't matter if the source data is in a table to start with.
Application.ScreenUpdating = False
Selection.Name = "myRng"
Set Qry1 = ActiveWorkbook.Queries.Add(Name:="QueerryZ", Formula:="let Source = Excel.CurrentWorkbook(){[Name=""myRng""]}[Content], #""Changed Type with Locale"" = Table.TransformColumnTypes(Source, {{""Column1"", type datetime}}, ""en-US"") in #""Changed Type with Locale""")
Set NewSht = ActiveWorkbook.Worksheets.Add
With NewSht.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Qry1.Name & ";Extended Properties=""""", Destination:=NewSht.Range("A1"))
With .QueryTable
.CommandType = xlCmdSql
.CommandText = "SELECT * FROM [" & Qry1.Name & "]"
.Refresh BackgroundQuery:=False
End With
Range("myRng").NumberFormat = "d/m/yyyy h:m:ss" ' AM/PM" 'This can be any format.
Range("myRng").Value = .DataBodyRange.Value
End With
Qry1.Delete
Application.DisplayAlerts = False: NewSht.Delete: Application.DisplayAlerts = True
ActiveWorkbook.Names("myRng").Delete
Application.ScreenUpdating = True
End Sub
@shrivallabha, in the UK it gave:Following formula works at my end:
=$F$3-INT(E7)
Curious if this works at your end @GraH - Guido @p45cal
@vletmshrivallabha
Try this ...
You offered =int(E7) to solve E-columns value -- okay?
What value it gives?
Here, it gives 43290 (O-column) = 09-Jul-2018 (P-column) -- okay?
>> E7 month is Aug and day is 7 = 07-Sep-2018 << -- okay
Note: F3 date is 08-Sep-2018 (= yesterday)
Cell E8 has value 8/30/2018 = 30-Aug-2018
if takes INT(E8) then #VALUE! ... won't understand date correct.
View attachment 55088
I've added 'my results' to F-column
F7: 'yesterday' - 07-Sep-2018 = 1 (Your formulas result is -29)
F8: 'yesterday' - 30-Aug-2018 = 9 (Your formulas result is -9)
... and so on (as in #13Reply)
> Questions? <
REENA GUPTA
This would be one possible ...
Change F3 -date or any 'Last Trans Times'-value ...
I think you would need to choose MDY rather than DMY - this is telling Excel how the source data is arranged.
Here in the UK I tried DMY and only got a removal of the time element. The cells remained formatted as Text and the dates as text strings.
Using MDY I got true dates formatted in my locale date format.