Discussions

Expand all | Collapse all

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

  • 1.  Converting Date From mm-dd-yyyy to dd/mm/yyyy

    Posted 27 days ago
    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
    ------------------------------


  • 2.  RE: Converting Date From mm-dd-yyyy to dd/mm/yyyy

    Posted 27 days 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
    ------------------------------



  • 3.  RE: Converting Date From mm-dd-yyyy to dd/mm/yyyy

    Posted 27 days 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
    ------------------------------



  • 4.  RE: Converting Date From mm-dd-yyyy to dd/mm/yyyy

    Posted 27 days 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
    ------------------------------



  • 5.  RE: Converting Date From mm-dd-yyyy to dd/mm/yyyy

    Posted 27 days ago

    Hi Mark

    It worked perfectly fine.

    Appreciate your help.



    ------------------------------
    Amandeep Dhillon
    ------------------------------