Solved How to change date portion at beginning

[Ashfaque]

Hi,

I have following date data received from a website. I collected these dates in Excel. Basically this is Immigration Entries at the airport that show a person has immigrated on so n so date and time. But airlines date format is typical like below.

00:29:19*2008-12-25
14:25:20*2009-09-17
21:05:00*2011-12-26
03:18:20*2014-01-17
16:13:13*2016-09-26
23:00:40*2017-03-11
19:23:22*2018-04-26
16:29:51*2018-11-10
21:01:23*2021-04-28

I need to appear this data in following manner so that I can utilize it for further process.

Monday 15:23 20/08/2018 15:23
Friday 14:16 19/02/2021 14:16
Saturday 01:11 31/12/2022 01:11

I want the date portion to appear first with / (not with - sign) and then time portion so I can display day and time of the entry in other column - as shown above.

Is there any function or code to do it?

Regards,
Ashfaque

 

[ebs17]

I think the first step should be to create a proper DateTime value from the string. In the second step you can format the DateTime value as you like.

Function GetDateTimeValue(ByVal AnyString As String) As Date
    Dim sArr() As String
    sArr = Split(AnyString, "*")
    GetDateTimeValue = CDate(sArr(1) & " " & sArr(0))
End Function

Sub call_GetDatetimeValue()
    Const cContent = "00:29:19*2008-12-25"
    Dim dtResult As Date
   
    dtResult = GetDateTimeValue(cContent)
    Debug.Print dtResult
    Debug.Print Format(dtResult, "dddd hh:nn"), Format(dtResult, "dd/mm/yyyy hh:nn")
End Sub

 

[arnelgp]

Format(Mid(fld, InStrRev(fld, "*") + 1) & " " & Left(fld, InStrRev(fld, "*") - 1), "mmmm hh:nn dd/mm/yyyy hh:nn")

 

[bob fitz]

Hi,

I have following date data received from a website. I collected these dates in Excel. Basically this is Immigration Entries at the airport that show a person has immigrated on so n so date and time. But airlines date format is typical like below.

00:29:19*2008-12-25
14:25:20*2009-09-17
21:05:00*2011-12-26
03:18:20*2014-01-17
16:13:13*2016-09-26
23:00:40*2017-03-11
19:23:22*2018-04-26
16:29:51*2018-11-10
21:01:23*2021-04-28

I need to appear this data in following manner so that I can utilize it for further process.

Monday 15:23 20/08/2018 15:23
Friday 14:16 19/02/2021 14:16
Saturday 01:11 31/12/2022 01:11

I want the date portion to appear first with / (not with - sign) and then time portion so I can display day and time of the entry in other column - as shown above.

Is there any function or code to do it?

Regards,
Ashfaque

Try:
Ans: WeekdayName(Weekday(Format(Right([dat],10),"dd/mm/yyyy"),0),0) & " " & Left([dat],5) & " " & Format(Right([dat],10),"dd/mm/yyyy") & " " & Left([dat],5)
where [dat] is the name of your data column in a query

 

[bob fitz]

Format(Mid(fld, InStrRev(fld, "*") + 1) & " " & Left(fld, InStrRev(fld, "*") - 1), "mmmm hh:nn dd/mm/yyyy hh:nn")

To return the name of the day, shouldn't that be:
Format(Mid$(fld, InStrRev(fld, "*") + 1) & " " & Left$(fld, InStrRev(fld, "*") - 1), "dddd hh:nn dd/mm/yyyy hh:nn")

 

[prabha_friend]

Hi,

I have following date data received from a website. I collected these dates in Excel. Basically this is Immigration Entries at the airport that show a person has immigrated on so n so date and time. But airlines date format is typical like below.

00:29:19*2008-12-25
14:25:20*2009-09-17
21:05:00*2011-12-26
03:18:20*2014-01-17
16:13:13*2016-09-26
23:00:40*2017-03-11
19:23:22*2018-04-26
16:29:51*2018-11-10
21:01:23*2021-04-28

I need to appear this data in following manner so that I can utilize it for further process.

Monday 15:23 20/08/2018 15:23
Friday 14:16 19/02/2021 14:16
Saturday 01:11 31/12/2022 01:11

I want the date portion to appear first with / (not with - sign) and then time portion so I can display day and time of the entry in other column - as shown above.

Is there any function or code to do it?

Regards,
Ashfaque

1. Select the Data.
2. Click the Data Tab in the Ribbon.
3. Click "Text-to-Columns" in the "Data Tools" Section.
4. Select "Delimited"
5. Click 'Next'
6. Tick 'Other' and Enter '*' in the adjacent textbox
7. Click 'Next'
8. Select any Date Format
9. Click 'Finish'
10. Select Column "A" and Format it to "Time Format"

 

[Ashfaque]

Thanks all of you gents.

Let me clear it again:

The date format I received in EXCEL sheet in one column is follows:

21:01:23*2021-04-24

I want this date to split into other 2 columns of same excel sheet:

in first column 24/04/2021 23:01:21 and in second column day of this date with time which is Saturday 23:01:21.

This why I want is to decide if the employee left on vacations on week-end so I shall count his real vacations starting from first working day which shall be from Monday onwards.

Since I am using excel rarely, please let me know where and how to use the functions "GetDateTimeValue" provided by ebs17 and Format provided by Arnel and Bob.

Thanks for your support again

 

[bob fitz]

If you have the data that you have posted here, in column A then use the following formula in column B:
=CONCAT(DAY(RIGHT(A2,10)),"/",MONTH(RIGHT(A2,10)),"/",YEAR(RIGHT(A2,10))," ",LEFT(A2,8))
And use the following formula in column C:
=CONCAT(TEXT(LEFT(B2,10),"dddd")," ",RIGHT(B2,8))

 

[Ashfaque]

If you have the data that you have posted here, in column A then use the following formula in column B:
=CONCAT(DAY(RIGHT(A2,10)),"/",MONTH(RIGHT(A2,10)),"/",YEAR(RIGHT(A2,10))," ",LEFT(A2,8))
And use the following formula in column C:
=CONCAT(TEXT(LEFT(B2,10),"dddd")," ",RIGHT(B2,8))

If I use your first formula line in B2 then "#NUM!" is appearing. my data is in A2

 

[bob fitz]

Can ou post a copy of your spreadsheet

 

[Gasman]

If I use your first formula line in B2 then "#NUM!" is appearing. my data is in A2

Break it down into small steps, then assemble those steps when it is working.
It only involves getting the correct positions and lengths for each function.?

 

[bob fitz]

The two formulas that I offered in post #9 worked for me.

 

[arnelgp]

for the date part, use
=DateValue(Right(A1,10))

for the time part:
=TimeValue(Left(A1,8))

 

[Ashfaque]

Yes, I did split formula as advised and as shown in sample db of Arnel. And it worked....

Thanks all of you guys.....