Counting dates

[deejay_totoro]

Hello,

I have a query which has a start date and an end date.

I would like to count the number of days inbetween the two dates so I used the following:

TotalDays: ([EndDate])-([StartDAte])

This works BUT! if the start and end date are the same, then a value of 0 is returned. What I need is a value of 1.

For example if someone was off work for 1 day, the start and end dates would be the same. So that why I need to represent this date as a 1.

How might I do this?

Thank you!

dj_T

 

[Jon K]

TotalDays: iif([EndDate]=[StartDate], 1, [EndDate]-[StartDate])

 

[deejay_totoro]

great

Thanks!

It works just fine.

dj_T

 

[deejay_totoro]

Weekends

Well actually,

I may need to do weekends.

Is that possble completely within a query? Or would I have to use VBA?

dj_T

 

[raskew]

Jon -

Depending on the situation, it can get a little bit stickier. From the debug window:

startdte = #11/27/03#
enddte = #11/27/03#
? startdte
11/27/03 
? enddte
11/27/03 
?  iif(EndDte = StartDte, 1, EndDte-StartDte)
 1 

startdte = #11/27/03#
enddte = #11/28/03#
? startdte
11/27/03 
? enddte
11/28/03 
?  iif(EndDte = StartDte, 1, EndDte-StartDte)
 1

Example: I consult to a juvenile detention center. Scenarios:

1) If a kid is admitted on day 1 (regardless if it's 00:01 or 23:59), that day is a billable day. If the kid is discharged on the same day as admitted, billing is for one day.

2) If the kid is admitted on day 1 and is discharged on day 2 (regardless if it's 00:01 or 23:59), both the admission and discharge days are billable.

Things that make you go hmmm...

Best wishes,

Bob

 

[Jon K]

Bob,

Since the poster had said "TotalDays: ([EndDate])-([StartDAte])   This works", I overlooked the logic behind the calculation and provided an expression to return 1 for StartDate = EndDate as required.

TotalDays: ([EndDate])-([StartDAte])
This works BUT! if the start and end date are the same, then a value of 0 is returned. What I need is a value of 1.

Pat has subsequently pointed out the inconsistency and provided a solution.

Hopefully, the poster has also found the code to eliminate weekends!


I think Pat's solution can be applied to the juvenile detention center scenarios perfectly.

The example makes me think of the charges imposed by the public hospitals in my country. They post a daily rate for hospitalised patients, but they charge them for 24-hour days. Hence a patient admitted and subsequently discharged within 24 hours is billed for one day no matter when he was admitted.

Jon