Calculate Dates with If Then Else Statements

I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 
years, etc.) in an Access 2003 database form.  This is what I am trying to 
do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the 
Full-Time End Date has a Date, use that Date.

Separately, these two formulas work:

=DateDiff("m",[FullTimeStartDate],Now())/12
=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12

My problem is writing a statement to calculate using the END DATE if one is 
entered, or NOW, if there is no end date.  I have tried many combinations, 
but can’t get it to work.  Is there a better way to do this?  I haven’t had 
much experience writing these types of statements.  HELP!

0
Utf
3/21/2007 7:00:30 PM
access.forms 6864 articles. 2 followers. Follow

7 Replies
1458 Views

Similar Articles

[PageSpeed] 11

Take a look at the IIF() function, if you are working in a query.

Look into the If...Then...Else statement if you are working in code behind 
the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"PolQueen" <PolQueen@discussions.microsoft.com> wrote in message 
news:8CEEC781-1A3C-4350-A16D-4F97A88913A6@microsoft.com...
>I need to calculate total years' full time experience (i.e. 2.5 years, 3.25
> years, etc.) in an Access 2003 database form.  This is what I am trying to
> do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the
> Full-Time End Date has a Date, use that Date.
>
> Separately, these two formulas work:
>
> =DateDiff("m",[FullTimeStartDate],Now())/12
> =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
>
> My problem is writing a statement to calculate using the END DATE if one 
> is
> entered, or NOW, if there is no end date.  I have tried many combinations,
> but can't get it to work.  Is there a better way to do this?  I haven't 
> had
> much experience writing these types of statements.  HELP!
> 


0
Jeff
3/21/2007 7:41:14 PM
Thank you for your quick reply.  Unfortunately, I never learned how to write 
code, and wasn't able to get an IIF() function to work on this.  I think I am 
missing a step. 


"PolQueen" wrote:

> I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 
> years, etc.) in an Access 2003 database form.  This is what I am trying to 
> do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the 
> Full-Time End Date has a Date, use that Date.
> 
> Separately, these two formulas work:
> 
> =DateDiff("m",[FullTimeStartDate],Now())/12
> =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
> 
> My problem is writing a statement to calculate using the END DATE if one is 
> entered, or NOW, if there is no end date.  I have tried many combinations, 
> but can’t get it to work.  Is there a better way to do this?  I haven’t had 
> much experience writing these types of statements.  HELP!
> 
0
Utf
3/21/2007 8:52:02 PM
Check Access HELP on the IIF() function.  Embed it in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"PolQueen" <PolQueen@discussions.microsoft.com> wrote in message 
news:5AEBCC01-7794-41AE-BEC1-3C6446E82485@microsoft.com...
> Thank you for your quick reply.  Unfortunately, I never learned how to 
> write
> code, and wasn't able to get an IIF() function to work on this.  I think I 
> am
> missing a step.
>
>
> "PolQueen" wrote:
>
>> I need to calculate total years' full time experience (i.e. 2.5 years, 
>> 3.25
>> years, etc.) in an Access 2003 database form.  This is what I am trying 
>> to
>> do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the
>> Full-Time End Date has a Date, use that Date.
>>
>> Separately, these two formulas work:
>>
>> =DateDiff("m",[FullTimeStartDate],Now())/12
>> =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
>>
>> My problem is writing a statement to calculate using the END DATE if one 
>> is
>> entered, or NOW, if there is no end date.  I have tried many 
>> combinations,
>> but can't get it to work.  Is there a better way to do this?  I haven't 
>> had
>> much experience writing these types of statements.  HELP!
>> 


0
Jeff
3/21/2007 10:02:24 PM
On Wed, 21 Mar 2007 12:00:30 -0700, PolQueen
<PolQueen@discussions.microsoft.com> wrote:

>I need to calculate total years� full time experience (i.e. 2.5 years, 3.25 
>years, etc.) in an Access 2003 database form.  This is what I am trying to 
>do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the 
>Full-Time End Date has a Date, use that Date.
>
>Separately, these two formulas work:
>
>=DateDiff("m",[FullTimeStartDate],Now())/12
>=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
>
>My problem is writing a statement to calculate using the END DATE if one is 
>entered, or NOW, if there is no end date.  I have tried many combinations, 
>but can�t get it to work.  Is there a better way to do this?  I haven�t had 
>much experience writing these types of statements.  HELP!

I'd use the NZ() function:

DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.

NZ will check the first argument - FullTimeEndDate - and return its value if
there is one, and if not it will return the second argument, Date() in this
case. Now() doesn't return today's date, but rather the current date and time
accurate to microseconds - you don't need that level of precision!

             John W. Vinson [MVP]
0
John
3/21/2007 10:43:18 PM
Nice touch, John!  Using Nz() to handle IIF() functionality!!

Jeff

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:m4d3039rb76h28edr8rg1r2p5r20ggabm9@4ax.com...
> On Wed, 21 Mar 2007 12:00:30 -0700, PolQueen
> <PolQueen@discussions.microsoft.com> wrote:
>
>>I need to calculate total years' full time experience (i.e. 2.5 years, 
>>3.25
>>years, etc.) in an Access 2003 database form.  This is what I am trying to
>>do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the
>>Full-Time End Date has a Date, use that Date.
>>
>>Separately, these two formulas work:
>>
>>=DateDiff("m",[FullTimeStartDate],Now())/12
>>=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
>>
>>My problem is writing a statement to calculate using the END DATE if one 
>>is
>>entered, or NOW, if there is no end date.  I have tried many combinations,
>>but can't get it to work.  Is there a better way to do this?  I haven't 
>>had
>>much experience writing these types of statements.  HELP!
>
> I'd use the NZ() function:
>
> DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.
>
> NZ will check the first argument - FullTimeEndDate - and return its value 
> if
> there is one, and if not it will return the second argument, Date() in 
> this
> case. Now() doesn't return today's date, but rather the current date and 
> time
> accurate to microseconds - you don't need that level of precision!
>
>             John W. Vinson [MVP] 


0
Jeff
3/21/2007 11:54:29 PM
On Wed, 21 Mar 2007 16:54:29 -0700, "Jeff Boyce" <nonsense@nonsense.com>
wrote:

>Nice touch, John!  Using Nz() to handle IIF() functionality!!

IIf(<condition just checks for Null>, <use NZ>, <use the full IIF>)


             John W. Vinson [MVP]
0
John
3/22/2007 1:39:43 AM
THANK YOU!  That worked perfectly.  I didn't know about NZ - it is great!
This is the first time I used this forum - you and Jeff were a big help.

Thanks again!
**********

"John W. Vinson" wrote:

> On Wed, 21 Mar 2007 12:00:30 -0700, PolQueen
> <PolQueen@discussions.microsoft.com> wrote:
> 
> >I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 
> >years, etc.) in an Access 2003 database form.  This is what I am trying to 
> >do:  If the Full-Time End Date is Blank, use Today's Date, ELSE if the 
> >Full-Time End Date has a Date, use that Date.
> >
> >Separately, these two formulas work:
> >
> >=DateDiff("m",[FullTimeStartDate],Now())/12
> >=DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12
> >
> >My problem is writing a statement to calculate using the END DATE if one is 
> >entered, or NOW, if there is no end date.  I have tried many combinations, 
> >but can’t get it to work.  Is there a better way to do this?  I haven’t had 
> >much experience writing these types of statements.  HELP!
> 
> I'd use the NZ() function:
> 
> DateDiff("m", [FullTimeStartDate], NZ([FullTimeEndDate], Date())) / 12.
> 
> NZ will check the first argument - FullTimeEndDate - and return its value if
> there is one, and if not it will return the second argument, Date() in this
> case. Now() doesn't return today's date, but rather the current date and time
> accurate to microseconds - you don't need that level of precision!
> 
>              John W. Vinson [MVP]
> 
0
Utf
3/22/2007 1:38:48 PM
Reply:

Similar Artilces:

Date construct with DatePart is kicking my.... well, you know.
Private Sub cmdCreatePath_Click() Dim strDateCons As String <bunch of my code that is (surprisingly) working like it should> strDateCons = DatePart(yyyy, Me.txtDateEntered) & "-" & DatePart(mm, Me.txtDateEntered) & _ "-" & DatePart(dd, Me.txtDateEntered) Debug.Print strDateCons End Sub When I click the magic button... nuffin'! I get an error message that states "Compile error: Variable not defined" and it highlights the 'yyyy' part of my first DatePart statement. What I actually wanted it to do was to take the date ...

if and and statements
Looking for help with a 2 x 4 matrix with the following conditions A1 B1 C1 Answer 3 0 1 A1*4+2 3 0 2 0 3 1 1 A1*2+1 3 1 2 0 =IF(AND(B1=0,C1=1),+A1*4*2,IF(AND(B1=0,C...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Changing a text field to a date field
Hi I know this is a problem for lots of people but I still cant find an answer. I have imported a text file into excel with a number field that displays credit card expiry dates as a 4 digit number. example 0408 so month + year. I have converted the number field to a text field so it doesnt drop off the first zero and now I need to convert it to a date field. Any nice easy simple ideas. Any help would be much appreaciated. Or direction to a question the same. Perhaps =DATE(100+RIGHT(A1,2),LEFT(A1,2),1) format as date -- daddylonglegs --------------------------------------------...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

date #4
Hi All I live in South Africa, and I have an interesting problem that persists in Excel 2003 and 2007. =TEXT(NOW(),"dd mmm yyyy") returns 15 Dec 2006 =month(now()) returns 12 =text(month(now()),"MMMM") returns January =year(now()) returns 2006 =text(year(now()),"yyyy") returns 1905 Why would this be? Thanks Chris Chris, =text(month(now()),"MMMM") should be =text(now(),"MMMM") and =text(year(now()),"yyyy") should be =text(now(),"yyyy") or just =year(now()) Otherwise, you are calcing the month of the 12th day afte...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

GL Posting date is missing or invalid error message
Hello, When trying to post a Sales order through Sales transactions entry window, we get an error message 'General ledger posting date is invalid or missing' The dates seem correct. The fiscal periods are all set up correctly. This happens whether we try to post an individual transcation or a batch. The batch looks like it is posting, but all the posting reports have zero mamounts in them & the batch still sits there even after posting as if it has never been posted. We are running GP 8.00g34 What is causing this & how do we fix it? Thanks Did you recently change your pos...

Bug in receiving entry. error calculating Prev Qty Shipped
Dynamics GP Bug Report, version 9.0 service pack 1. Version Information: 9.00259 Location: Receiving Transaction Entry Description: As one enters receiving transactions lines and adjusts the Qty Shipped, the quantity previously shipped is calculated incorrectly. This PO transaction was for a quantity of 120. A quantity of 15 were received on a previous shipment. The Qty previously shipped should display 120-15-30=75 not 45. To make this error occur continue to change the Qty Shipped. Sometimes the error will occur quickly, other times it might take 25 changes. Products Load...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

NESTED IF STATEMENT
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C85845.4A66D4E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have to calculate commission on sales tax collected based on the = following criteria: 1) If sales tax collected is $11.00 or less, then the commission =3D to = the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission=3D = $11.00 3) If sales tax collected is more than $333.33, then commission =3D 3.3% = of the sales tax collected, with a maximum amount =3D $99...

IF Statement 01-17-08
Hi, I am trying to run a query that has 4 fields: location, type, year, and cost. However I need to increase the cost for the year 2007 for 2 types. The IF statement I entered into the cost field is: TotalCost:IIF([Type]="C" Or "H" And [Year]=2007,[SumCost]/.9,[SumCost]) The issue I am having is that it is increasing the costs for all types. Is there a way to fix this or do i need a new query for C and H and then combine the 2 queries? Thanks for any help you can provide. Actually, you don't need to do it that way. Create a Where clause for the query tha...

Date Formula #3
I am having difficulty with the "NOW" formula. What I would like for it to do is when I enter a value in one cell have it update in another cell the date when the information was entered and that date stay permanenty. What I am having is the date changes to the current date everyday. Is this possible to do? Any help would be appreciated. Thanks. Todd You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose ...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

SUMIFS with Dates
I have a conditional SUMIFS Detail Sheet Type Date Qty Credit 2/1/10 1 Sales 2/2/10 2 Credit 2/15/10 3 Summary Sheet Week # Start Date End Date Credits Sales 1 2/1/10 2/5/10 1 2 2 2/8/10 2/15/10 3 0 So, basicallay I am summing the Credit column if the Detail is Type Credit and the Date is >= the Start Date on the line and the End Date <= the Date on the line. Same for Sales. However, as simple as it seems, it just seems not lo l...

Dynamics CRM 4.0
Hi, I've modified the format in the system settings screen to be "English (United Kingdom)", however all dates are still appearing in US format. Even when you expand the calendar control it displays incorrectly. Numeric values are correct - showing the pound sign and correct decimal point. I've restarted IIS, made sure all SQL logins are British English and the default regional settings of the server are set to British English. Even rebooted the server but this still hasn't corrected the problem. Can anybody tell me how to resolve this please Thanks I was looking ...

email sent/received dates
Hi, I'm using MSN Premium included with Verizon DSL and decided to take advantage of the 2g email storage and outlook connector for msn. Got it set up and working. I created a new folder to archive some email before I copy it to the archive pst on my pc so I can search it remotely. No problems. Today, I noticed all my dates changed on sent and received email that I moved. Worse, the header in the email (that outlook displays) shows this new date and doesn't reflect the original email date. I was moving mail from our company exchange server that was ready to be deleted to this fo...

range color from date and database
newbie done some homework on excel i would like to have a range change color based on date and time and a name from a database--or any suggestion for example if it is thursday and the time is betweeen 1 and 3 pm i want the range to be say light red if out of tinme range i want it to be light green also the cell above the range as long as time constraints are met to have a name from a datasource any suggestions or ideas apprciated i need to have data from either a database or from say another sheet where user can enter a form the data from the datasource would fill range values a...

Date Part or Date Value
I have a form such that there are labels for each of the month and the control being a checkbox such that if a task is done in a particular month, then the checkbox for this month is ticked. I would like to add a textbox on this form and link it to a table such that if the checkbox for ie. Jan is checked then the textbox should show Jan-2008 if the year entered was 2008. This would then be stored in a table to use for calculations later on. Please help. On Sun, 3 Feb 2008 20:29:27 -0800 (PST), c8tz <ccholai@gmail.com> wrote: >I have a form such that there are labels for each of t...

Date Selection on HR Training Reports
I am implementing Human Resources, Advanced Human Resources and Certifications, Licensing and Training On the standard HR training reports such as Employee Classes, date selection does not work. If I enter a date selection, nothing is printed on the report. Without date selection, everything prints. I found a KB article - 863263 that states this is an issue for V 8.0 but I can't find anything more frequent. This appears to still be an issue. Does anyone have an update or are you experiencing this as well. Thanks Becker, If you are a microsoft partner, You can log into Dynamics GP...

Change date format in several worksheets
I have about 50 worksheets in a workbook and would like to change the date format in all. Is there a way to change all at once or must it be done worksheet by worksheet? Select first worksheet. Right-click on the tab and "select all sheets" to group them. Select data range in activesheet. CRTL + A(twice in XL 2003) will select all cells. Make your Format changes. What is done to one sheet will be done to all. DO NOT FORGET! to right-click on any tab and "ungroup" before making any other changes unless you want to change all sheets. Gord Dibben Excel MVP On Sat...

Date Math in Large Column
Hello! I have a huge spreadsheet (32000 rows). I have done a subtotal on part number. This does a sum on several other columns of values. There is a date column (B) with a date for each transaction for each part number. I am trying to come up with a way to determine the number of months over which the transactions occurred for each part number. The problem is that each subtotalled "group" is a different number of rows. I started to manually feed in this formula and then modify it for each group, but this could take days. =(max(b2:b20)-min(b2:b20))/30 This gives me ...