5 Replies Latest reply: Oct 2, 2012 7:08 AM by Jonathan Dienst

# Calculated Dimension measuring between start date and no end date

I'm struggling with creating a calculated demension to measure the running time between two date fields.

Recieved Date

Sent Date

I want to display the running count in days when the Sent Date is 'None'

This is what I was working with:

=if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=1,'Same Day',

if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=2,'1 Day',

if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=3,'2 Day',

if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=4,'3 Day',

if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=5,'4 Day',

if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=6,'5 Day','Over 5 Days'))))))

• ###### Re: Calculated Dimension measuring between start date and no end date

u can use today() if its none

• ###### Re: Calculated Dimension measuring between start date and no end date

Hi

Networkdays takes 2 dates as parameters, you are passing a date and a boolean (0/1) so this expression is not going to give you the results you expect. Which date should be the second date when the sent date is none?

```If(WP_Consult_Table.DateSent = 'None',
If(NetWorkDays(WP_Consult_Table.CreationDate, Today()) < 6,
Pick(NetWorkDays(WP_Consult_Table.CreationDate, Today()), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),
'Over 5 Days'
),

If(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent) < 6,
Pick(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),
'Over 5 Days'
)
)
```

But I would stronly advise that you put this in script rather than a calculated dimension.

Hope that helps

Jonathan

• ###### Re: Calculated Dimension measuring between start date and no end date

Thanks Jonathan!

• ###### Re: Calculated Dimension measuring between start date and no end date

Hi Jonathan,

How would I use the same expression but calculate time in hours?

`If(WP_Consult_Table.DateSent = 'None',          If(NetWorkDays(WP_Consult_Table.CreationDate, Today()) < 6,                    Pick(NetWorkDays(WP_Consult_Table.CreationDate, Today()), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),                    'Over 5 Days'          ),            If(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent) < 6,                    Pick(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),                    'Over 5 Days'          ))`

• ###### Re: Calculated Dimension measuring between start date and no end date

Rob

I dont understand your question. Perhaps you should make an attempt and I could assist you. But please raise this in a new post. This one has been marked as answered, and you will get a better response in a new post. But please elaborate some on your requirement.

Regards

Jonathan