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

Question

HelloI am trying to convert the date from mm-dd-yyyy to dd/mm/yyyy and i have used following formulaLeft(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------------------------------

MarkShnierYou · Answer

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

Sign up

Login to the community