Formatting dates

• Hi All,

I have a value of the field, ProcessDate as 2016-05-18 19:27:40

I need to format 2016-05-18 as 1160518 - Pdate and 19:27 to 19 - PHour and 27 - Pmin

How do I do this in one single function or in three steps using VBA?

[TABLE="width: 547"]

[tr]

[/tr]

[/TABLE]
[TABLE="width: 124"]

[tr]

[TD="class: xl82, width: 124"]Thanks,
pragov
[/TD]

[/tr]

[/TABLE]

• Re: Formatting dates

Try this formula.

Code
``=11&TEXT(MID(A1,FIND("-",A1)-4,99),"mmdd")&" - Pdate "&TEXT(INT(MOD(--MID(A1,FIND("-",A1)-4,99),1)*24),"0")&" - PHour "&TEXT(INT(MOD(MOD(--MID(A1,FIND("-",A1)-4,99),1)*24,1)*60),"0")&" - Pmin"``

[TABLE="class: grid"]

[tr]

[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A

[/TD]
[TD="bgcolor: #DCE6F1"]

B

[/TD]

[/tr]

[tr]

[TD="bgcolor: #DCE6F1"]

1

[/TD]

[td]

ProcessDate as 2016-05-18 19:27:40

[/td]

[td]

110518 - Pdate 19 - PHour 27 - Pmin

[/td]

[/tr]

[/TABLE]

• Re: Formatting dates

I think the logic for the date is a constant "1" followed by the date formatted as YYMMDD:

="1"&TEXT(A1,"YYMMDD") obviously a bit more complex in the actual solution, just showing as an example

other than that, brilliant formula

• Re: Formatting dates

How do I do this in VBA?
TEXT and FIND work in Formula, but not in VBA.

• Re: Formatting dates

I was able to solve it

Code
``````Date=     Format(Mid(A1, 1, 19), "yymmdd")
Hour=     Format(Mid(A1, 1, 19), "Hh")
Minute=     Format(Mid(A1, 1, 19), "Nn")``````

pragov

• Re: Formatting dates

Quote from S O;771743

I think the logic for the date is a constant "1" followed by the date formatted as YYMMDD:

="1"&TEXT(A1,"YYMMDD") obviously a bit more complex in the actual solution, just showing as an example

other than that, brilliant formula

Good catch. I lost the "6" in the "brush". Thanks for the backup.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!