IF formula where the input properties are datetime and date (and other issues with Line Item Start)

[PBaxter]

Hi All,

Anyone had any joy working with the  Line Item "start date" property?

It's causing me heartache. 

Simply, I want to create a Line Item / Deals report with a date field that either shows the "start date" (if that property is populated with a date) or the deal close date (if it isn't).

Problem (i) - in a custom report the "start date" returns null for *all* line items, even if populated,  if Deals is selected as the primary table and Line Items linked.   

If you make Line Items primary table, the report does finally return the "start date" correctly where populated with a date. But where the "start date" is set to "At payment", a null field is returned (I was hoping I would get the deal close date).

Plan B was to create a calculated field in Data Sets. I went with a formula like this (already knowing the ="" is incorrect):

IF([LINE_ITEM.hs_recurring_billing_start_date]="",[DEAL.closedate],[LINE_ITEM.hs_recurring_billing_start_date])

This throws two errors:

"EQUAL expects arguments 1 and 2 to be the same type. You provided Date property and ""

I can't figure out how to test for that field being empty (I've tried 'is nothing', 'is unknown' and every other combination I could think of) - unless there's a formula to test for emptiness? Any ideas?

Secondly:

IF expects arguments 2 and 3 to be the same type. You provided Datetime property and Date property.

So the two date properties are actually different data types. Anyone know how I can convert a Datetime to Date or vice versa?

Thanks if anyone can help! Or suggest an alternative method. Will save me some gray hairs.

Phil