Forum Discussion

AmandeepDhillon's avatar
AmandeepDhillon
Qrew Member
4 years ago

Converting Date From mm-dd-yyyy to dd/mm/yyyy

Hello

I am trying to convert the date from mm-dd-yyyy to dd/mm/yyyy and i have used following formula


Left(NotLeft(ToText([Last Drop Status Change Date]),"-"),"-")& "/" &
Case(Left(ToText([Last Drop Status Change Date]),2),
"01","01","02","02","03","03","04","04","05",
"05","06","06","07","07","08","08","09","09",
"10","10","11","11","12","12")& "/"
& Right(ToText([Last Drop Status Change Date]),"-")



My issue is that it works on all the fields that have date values but with blank fields it returns the value"/ /".
Can someone help me in figuring this out. I was thinking of using IF and else with this formula but its not compatible.
I would appreciate all the help and insights.

------------------------------
Amandeep Dhillon
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    When you want to concatenate separate fields or phrases together with the same characters , use the List function.

    for example
    List(" ", [first name, [middle name], [last name])

    The above will separate any non-blank fields with a space so that if the person has no middle name then it would just be first space last.


    In your case.
    List("\",

    Left(NotLeft(ToText([Last Drop Status Change Date]),"-"),"-"),

    Case(Left(ToText([Last Drop Status Change Date]),2),
    "01","01","02","02","03","03","04","04","05",
    "05","06","06","07","07","08","08","09","09",
    "10","10","11","11","12","12"),

    & Right(ToText([Last Drop Status Change Date]),"-")
    )

    But a better way would be this (not tested)

    List("-",
    PadLeft(ToText(Day([Last Drop Status Change Date])),2,"0"),
    PadLeft(ToText(Month([Last Drop Status Change Date])),2,"0"),
    ToText(Year([Last Drop Status Change Date])))


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AmandeepDhillon's avatar
      AmandeepDhillon
      Qrew Member
      4 years ago
      Hi Mark

      Thanks for your response.

      The first solution has error- Cannot use "-" operator on types text.
      whereas with second solution result is  dd-mm-yyyy and blank fields as 00-00 whereas my specific requirement is dd/mm/yyyy

      Please help.

      ------------------------------
      Amandeep Dhillon
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Try this

        var text Result = List("/",
        PadLeft(ToText(Day([Last Drop Status Change Date])),2,"0"),
        PadLeft(ToText(Month([Last Drop Status Change Date])),2,"0"),
        ToText(Year([Last Drop Status Change Date])));

        If(not IsNull([Last Drop Status Change Date]), $Result)

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------