Solved How to change date portion at beginning (1 Viewer)

Ashfaque

Student
Local time
Tomorrow, 02:10
Joined
Sep 6, 2004
Messages
894
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

Well-known member
Local time
Today, 21:40
Joined
Feb 7, 2020
Messages
1,752
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.

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:40
Joined
May 7, 2009
Messages
18,998
Format(Mid(fld, InStrRev(fld, "*") + 1) & " " & Left(fld, InStrRev(fld, "*") - 1), "mmmm hh:nn dd/mm/yyyy hh:nn")
 

bob fitz

AWF VIP
Local time
Today, 20:40
Joined
May 23, 2011
Messages
4,710
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

AWF VIP
Local time
Today, 20:40
Joined
May 23, 2011
Messages
4,710
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

Prabhakaran Karuppaih
Local time
Tomorrow, 02:10
Joined
Mar 22, 2009
Messages
770
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

Student
Local time
Tomorrow, 02:10
Joined
Sep 6, 2004
Messages
894
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

AWF VIP
Local time
Today, 20:40
Joined
May 23, 2011
Messages
4,710
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

Student
Local time
Tomorrow, 02:10
Joined
Sep 6, 2004
Messages
894
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:40
Joined
Sep 21, 2011
Messages
13,888
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.? :(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:40
Joined
May 7, 2009
Messages
18,998
for the date part, use
=DateValue(Right(A1,10))

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

Attachments

  • Book1.zip
    8 KB · Views: 79

Ashfaque

Student
Local time
Tomorrow, 02:10
Joined
Sep 6, 2004
Messages
894
Yes, I did split formula as advised and as shown in sample db of Arnel. And it worked....

Thanks all of you guys.....
 

Users who are viewing this thread

Top Bottom