# Thread: Count Days In-between Two Date Fields In Report

1. ## Count Days In-between Two Date Fields In Report

I am trying to figure out how to count the number of days in between two date fields by using a calculated field in a report. I can simply use Date2 - Date1 to get my result but it always 1 day short so I added Date2-Date1 + 1 and it gets me my results but if there is not any dates in the Date1 and Date2 fields then it returns "1" and I want the field to just be null. I am thinking it will be a If/Then statement but cant figure it out for a reports calculated field.

Any Help Would Be Great.

2. ## Re: Count Days In-between Two Date Fields In Report

Originally Posted by tommyrotten
I am trying to figure out how to count the number of days in between two date fields by using a calculated field in a report. I can simply use Date2 - Date1 to get my result but it always 1 day short so I added Date2-Date1 + 1 and it gets me my results but if there is not any dates in the Date1 and Date2 fields then it returns "1" and I want the field to just be null. I am thinking it will be a If/Then statement but cant figure it out for a reports calculated field.

Any Help Would Be Great.
Try
if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,"")

3. ## Re: Count Days In-between Two Date Fields In Report

Thanks but that did not work as well. I loaded that into the expression builder and it would not save, as it said the expression is not valid. Ill do some research on "dtoc" and see if I can get somewhere from there.

4. ## Re: Count Days In-between Two Date Fields In Report

if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,0)

5. ## Re: Count Days In-between Two Date Fields In Report

Originally Posted by Al Buchholz
if(dtoc(date2)<>"" .and. dtoc(date1)<>"",date2 - date1 + 1,0)
That worked but it is still returning 1 and not 0 when it evaluates. If i remove the "+ 1" out of the equasion then it evals to "0" as it should. I am thinking that it does not like the "+ 1"

But thank you for the help. I am going to use it and try to work out the + 1 issue in spare time.

6. ## Re: Count Days In-between Two Date Fields In Report

dtoc() on an empty date field yields
Code:
`"  /  /    "`
Try
Code:
`if(date2<>{} .and. date1<>{},date2 - date1 + 1,0)`

7. ## Re: Count Days In-between Two Date Fields In Report

Originally Posted by Tim Kiebert
dtoc() on an empty date field yields
Code:
`"  /  /    "`
Try
Code:
`if(date2<>{} .and. date1<>{},date2 - date1 + 1,0)`
That did it. Thank you very much :D What is the theory behind using {}. I am trying to learn as I program and would love to know.

8. ## Re: Count Days In-between Two Date Fields In Report

The {} are used to express date values in xbasic the same way quotes are used to express character. So "" means an empty string. And {} means an empty date.

Code:
```dim d1 as d
d1 = {01/01/2001}

?d1
= {01/01/2001}

d1={}
?d1
= {  /  /    }```

9. ## Re: Count Days In-between Two Date Fields In Report

if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,"") or if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,0).

Your choice if you want "" or 0.

10. ## Re: Count Days In-between Two Date Fields In Report

Tommy
Just a fundamental question. I'm interested in your reasoning or the application.
In years of programming in many languages (alpha5 I'm pretty new at, but still supporting legacy systems),
the date functions return the same. date-date = days between.
You want never to return one day.

Number of days between say, midday on the 21st and midday on the 22nd is one day , not two.

If I did what you ask for a rental agent or hotel they wouldnt have any business.

Ray

11. ## Re: Count Days In-between Two Date Fields In Report

if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,"") or if(remspecial(dtoc(date2))<>"" .and. remspecial(dtoc(date1))<>"",date2 - date1 + 1,0).

Your choice if you want "" or 0.
Not if the field is defined as a numeric.... which is what date2 - date1 + 1 will return.

Can't have one part of an if return a numeric and the other part a character.

12. ## Re: Count Days In-between Two Date Fields In Report

Now to push a little further.
In a grid, I want the user to have the option of saying "end date = null".

I know it won't be that simple, but does anybody know what needs to be entered ? ( I tried <> {})

13. ## Re: Count Days In-between Two Date Fields In Report

Now to push a little further.
In a grid, I want the user to have the option of saying "end date = null".
I know it won't be that simple, but does anybody know what needs to be entered ? ( I tried <> {})
Hi Gregg (sorry Tommy)
Off the cuff I would have thought to try " / / "
so I tried the following

DIM SHARED date1 as D
date1=" / / "
?date1
= { / / }

So it should work.
Ray

14. ## Re: Count Days In-between Two Date Fields In Report

in fact set date1 = "" with nothing (or anything between quotes) makes date1 return { / / }
I don't mean to be finickiticular but this is useful (to me too)

15. ## Re: Count Days In-between Two Date Fields In Report

Turns out that for what I was trying to accomplish, is null works correctly if that is the only option, but I still want a way to show my users that they can do a combination (ex: is null or 01/31/2010).

16. ## Re: Count Days In-between Two Date Fields In Report

Originally Posted by Ray in Capetown
Tommy
Just a fundamental question. I'm interested in your reasoning or the application.
In years of programming in many languages (alpha5 I'm pretty new at, but still supporting legacy systems),
the date functions return the same. date-date = days between.
You want never to return one day.

Number of days between say, midday on the 21st and midday on the 22nd is one day , not two.

If I did what you ask for a rental agent or hotel they wouldnt have any business.

Ray
I am building an app that is used for OSHA (Occupational Safety Health Association)injury record keeping. I do recognize that date-date is the number of days in between. I was trying out a few different scenarios and was frustrated because I couldn't make it work and "I KNEW" there was a solution. That is all. That is the type A in me.

17. ## Re: Count Days In-between Two Date Fields In Report

Turns out that for what I was trying to accomplish, is null works correctly if that is the only option, but I still want a way to show my users that they can do a combination (ex: is null or 01/31/2010).
Please forgive the non accuracy in my code. I am not on my normal computer to prove it out.

What if.... You have a check box that the user selects for Null. In the date fields "client side properties" under "calculated field expression" put a "if" statement that evaluates to null if true or the date if false. if(checkbox1=.t.,date1="",date1<>{}) ????

18. ## Re: Count Days In-between Two Date Fields In Report

I haven't tried the checkboxes yet.
I guess that could make things easier for the users.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•