Calculate Number Of Days

[jereece]

In a database, I have the following date fields that I use to track projects that start and stop.

START1
STOP1
START2
STOP2
START3
STOP3

Is there a way I can use a query to calculate the total days the project was worked on? For example if I enter 1/1/06 in START1, 1/10/06 in STOP1, 2/1/06 in START2 and 2/15/06 in STOP2, 3/1/06 in START3 and 3/30/06 in STOP3, I need to calculate the total days. I need it to work regardless if there are 3 start/stops, 2 or just one start/stop.

I really appreciate the help.

Jim

 

[KeithG]

Yes, you should use the DaysBetween function in a query.

 

[jereece]

Yes, you should use the DaysBetween function in a query.

Can you provide a few more details? How would I write that in the Query Field?

Thanks,
Jim

 

[KeithG]

I'm sorry I meant the DateDiff function.

DateDiff("d",[Start1],[Stop1])

Type that into the query as if your were adding a field

 

[ColinEssex]

How do you calculate a project was worked on for only half a day? - charge for a full day anyway?

Col

 

[jereece]

How do you calculate a project was worked on for only half a day? - charge for a full day anyway?

I will. Fortunately for them this is not for anything related to pay. They are salary folks anyway. But I think it's stupid that we have a 60 day clock to get projects done, but management allows that clock to be started and stopped. Go figure

Thanks for the help. I will give it a shot. By the way, since I have 3 starts and stops, do I write it like this,

DateDiff("d",[Start1],[Stop1],[Start2],[Stop2],[Start3],[Stop3])

Jim

 

[Jon K]

Number of Days: DateDiff("d",[Start1],[Stop1]) +DateDiff("d",[Start2],[Stop2]) +DateDiff("d",[Start3],[Stop3])


Values in date fields are internally stored as numbers.
Alternatively you can use:-

Number of Days: [Stop1]-[Start1] +[Stop2]-[Start2] +[Stop3]-[Start3]
.

 

[jereece]

I hate to make this more complicated, but can I integrate this with an IIf statement so that if the status field is "In Progress" it calculates the days open using todays date minus the start date, otherwise it calculates the start and stop dates as described above. Bottom line, should the statement below work?

DAYS_OPEN: IIf([Status]="In Progress",Date()-[START_DATE],[Stop1]-[Start1] +[Stop2]-[Start2] +[Stop3]-[Start3])

Thanks,
Jim

 

[EMP]

DAYS_OPEN: IIf([Status]="In Progress", Date()-[START_DATE], [Stop1]-[Start1] +[Stop2]-[Start2] +[Stop3]-[Start3])

If [START_DATE] is a date field in the table, the expression should work.


However, if some of the fields may contain Null values, you will need to use the Nz() function to convert the Null values to zeros in the calculation.

DAYS_OPEN: IIf([Status]="In Progress", Date()-[START_DATE], Nz([Stop1])-Nz([Start1]) +Nz([Stop2])-Nz([Start2]) +Nz([Stop3])-Nz([Start3]))

^

 

[jereece]

After some thinking, I tried to simplify this a little. Basically what I am trying to do is calculate the number of days a project is open given a Start Date and the ability to stop and start the counting date several times. So here's what I settled on:

DAYS_OPEN: Nz([STOP_DATE1])-Nz([START_DATE])+Nz([STOP_DATE2])-Nz([START_DATE2])+Nz([APPROVAL_DATE])-Nz([START_DATE3])

The problem I have is that when I have a start date, but no stop date yet, the DAYS_OPEN is blank. As soon as I enter a stop date in the next field, the DAYS_OPEN then populates correctly.

Any idea why this is happening and what I can do?

Thanks,
Jim

 

[jereece]

Can anyone help me with my final delima?

Thanks,
Jim

 

[jereece]

I guess no takers? Any suggestions?

Thanks,
Jim

 

[KeithG]

Try using NZ([Start Date],0). Then trying changing all your NZ functions like this. Now when the field is Null it will return a zero.

 

[jereece]

Try using NZ([Start Date],0). Then trying changing all your NZ functions like this. Now when the field is Null it will return a zero.

Can you expain this a little further? Maybe plug it into my current calculation: DAYS_OPEN: Nz([STOP_DATE1])-Nz([START_DATE])+Nz([STOP_DATE2])-Nz([START_DATE2])+Nz([APPROVAL_DATE])-Nz([START_DATE3])

I really appreciate the help.

Jim

 

[raskew]

Hello -

Did we not do this here ? http://www.access-programmers.co.uk/forums/showthread.php?t=109896

Bob