Date calculations in days

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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)
 
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

Code to Calculate Days 1
Calculate and store future date in a table 14
Calculate the number days from previous record 7
Turnaround Time without Weekend 1
Calculate days elapsed in Access? 5
Calculated Dates 3
Date Calculation 4
Transfer records to Excel worksheet 1

Back
Top