Having trouble concatenating date fields 2

[ifthenelsenull]

CRYSTAL XI

I want to concatenate datetime fields with a slash between them and change the field from datetime to date.

I tried {Staff_V1;1.StartDateTime}&" / "&{Staff_V1;1.EndDateTime}and the formula field is blank in my query. Changing that to add totext({Staff_V1;1.StartDateTime}, "MM/dd/yyyy") is causing the same issue. I'm not actually sure what is going on here to cause the blanks.

Thanks

 

[Turkbear]

If either field is blank the entire string from the formula will be blank..have you checked for thst?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

You can also just place the fields in a text box format the datetime field to show date and manually type the /

Ian

 

[ifthenelsenull]

@turkbear That explains the blanks

@IanWaterman That's what I've been doing but I was wondering if there was a way to do that in a formula like:

IF ISNULL({Staff_V1;1.EndDateTime}) or {Staff_V1;1.EndDateTime} ="" then "n/a" else {Staff_V1;1.EndDateTime};

date({Staff_V1;1.StartDateTime})&" / "& date({Staff_V1;1.EndDateTime})

That formula isn't working but am I on the right track?

 

[IanWaterman]

Try

IF ISNULL({Staff_V1;1.EndDateTime}) or {Staff_V1;1.EndDateTime} ="" then "n/a" else
totext(date({Staff_V1;1.StartDateTime}),"MM/dd/yyyy") &" / "& totext(date({Staff_V1;1.EndDateTime}),"MM/dd/yyyy")

Ian

 

[ifthenelsenull]

Thanks for your help. The final working solution is:

IF ISNULL({Staff_V1;1.VisitEndDateTime}) or totext({Staff_V1;1.VisitEndDateTime}) ="" then
(date({Staff_V1;1.VisitStartDateTime})& "/ N/A") else
totext(date({Staff_V1;1.VisitStartDateTime}),"MM/dd/yyyy") &" / "& totext(date({Staff_V1;1.VisitEndDateTime}),"MM/dd/yyyy")

this is way more work than just combining them in a text box but I wanted to figure it out. I appreciate the help!

 

[IanWaterman]

Some times its worth doing as a formula. If you want to export to Excel and field is truncated, then a text box is truncated on export to excel whereas a formula will export all data.

Ian