Date calculations in days

G

Guest

I have an existing Access report that I need to add some date calculations
to. The report is a turnaround time report of a process. During the process
what I'm tracking goes from person to person. I need the days between each
step caldulated. There are 5 different dates:

#1 - Date Rec'd by Rev1
#2 - Date Review Sent to Rev2
#3 - Date Review Rec'd from Rev2
#4 - Date Committee Reviewed
#5 - Close Date

I need to be able to calculate the days between #1 and #5, #1 and #2, #2 and
#3 and #3 and #4. Also, not all of the fields may have data - there are
times when #2, #3 and #4 will not be filled in.

Is there an easy way to do this? Thanks in advance for your help.
 
G

Guest

Try using the datediff function, with IIF to check there are no null values,
if there are it will return 0

===================================================
IIF (not isnull([Date Rec'd by Rev1]) and not isnull([Close
Date]),datediff("d",[Date Rec'd by Rev1],[Close Date]),0)
===================================================
IIF (not isnull([Date Rec'd by Rev1]) and not isnull([Date Review Sent to
Rev2]),datediff("d",[Date Rec'd by Rev1],[Date Review Sent to Rev2]),0)
===================================================
IIF (not isnull([Date Review Sent to Rev2]) and not isnull([Date Review
Rec'd from Rev2]),datediff("d",[Date Review Sent to Rev2],[Date Review Rec'd
from Rev2]),0)
===================================================
IIF (not isnull([Date Review Rec'd from Rev2]) and not isnull([Date
Committee Reviewed]),datediff("d",[Date Review Rec'd from Rev2],[Date
Committee Reviewed]),0)
 
G

Guest

Thanks for your help with this - it looks like it's giving me the figures
that I need.

Ofer said:
Try using the datediff function, with IIF to check there are no null values,
if there are it will return 0

===================================================
IIF (not isnull([Date Rec'd by Rev1]) and not isnull([Close
Date]),datediff("d",[Date Rec'd by Rev1],[Close Date]),0)
===================================================
IIF (not isnull([Date Rec'd by Rev1]) and not isnull([Date Review Sent to
Rev2]),datediff("d",[Date Rec'd by Rev1],[Date Review Sent to Rev2]),0)
===================================================
IIF (not isnull([Date Review Sent to Rev2]) and not isnull([Date Review
Rec'd from Rev2]),datediff("d",[Date Review Sent to Rev2],[Date Review Rec'd
from Rev2]),0)
===================================================
IIF (not isnull([Date Review Rec'd from Rev2]) and not isnull([Date
Committee Reviewed]),datediff("d",[Date Review Rec'd from Rev2],[Date
Committee Reviewed]),0)


MCK said:
I have an existing Access report that I need to add some date calculations
to. The report is a turnaround time report of a process. During the process
what I'm tracking goes from person to person. I need the days between each
step caldulated. There are 5 different dates:

#1 - Date Rec'd by Rev1
#2 - Date Review Sent to Rev2
#3 - Date Review Rec'd from Rev2
#4 - Date Committee Reviewed
#5 - Close Date

I need to be able to calculate the days between #1 and #5, #1 and #2, #2 and
#3 and #3 and #4. Also, not all of the fields may have data - there are
times when #2, #3 and #4 will not be filled in.

Is there an easy way to do this? Thanks in advance for your help.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to sum between specific date And Date() 0
Date Calculation between Min & Max 1
Excel Help with dates 2
Advanced Problem: How Do I Make a, Multiple Date, Date Counter 5
Counting Days between two Dates excluding weekends 2
Having trouble with Date calculation Query logic 9
Counting days elapsed 7
Calculate working days between records 3

Top