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 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!