Date Brackets as an IIf statement
Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets are: Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = > 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary
|
0 |
|
Utf
4/21/2010 5:24:03 PM
access.queries
6343 articles. 
1 followers.
3 Replies
666 Views
NOTE: I have wrote my statement as follows: Age_DT_CD: IIf([# of Days left]<=0,"5",IIf([# of Days left] Between 0 And 30,"4",IIf([# of Days left] Between 31 And 90,"3"),IIf([# of Days left] Between 91 And 180,"2"),IIf([# of Days left] >180,"1"))))) Currently "Gary" wrote: > Looking for help writing two IIF queries that will put some dates into > brackets. I have some sku's that have a expiration date and the idea is to > put the product into 1 of 5 brackets. The brackets are: > > Brackets: > Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", > = > 181 days > Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 > up to 180 days > Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 > up to 90 days > Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 > Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0 > > I have the following in my data: Production Date, Shelf Life, and Expiration > Date. Can someone help me make two fields: Age Date Bracket Code and a Age > Date Bracket Description? > > Test Data is as follows: > Production DT Shelf Life Expiration DT Age DT CD Age DT DESC > 12/22/2009 365 12/20/2010 1 New Product > 10/22/2009 365 10/22/2010 1 New Product > 10/21/2009 365 10/21/2010 2 Mild, 6 to > 3 months left > 06/21/2009 365 06/21/2010 2 Mild, 6 to > 3 months left > 06/20/2009 365 06/20/2010 3 Moderat, 3 > to 1 months left > 05/22/2009 365 05/20/2010 3 Moderat, 3 > to 1 months left > 05/21/2009 365 05/21/2010 4 Critical, 1 > months left > 10/24/2009 180 04/22/2010 4 Critical, 1 > months left > 10/23/2009 180 04/21/2010 5 Expired > > Any help create a IIF statement in my query is greatly appreciated. > > Regards, > Gary
|
|
0 |
|
Utf
4/21/2010 6:11:03 PM
Once you next about 3 IIf's, things get hard to maintain. Instead you can use
Select Case within a function inside a module. Then you can call upon it in a
query/SQL statement. Below is an example.
Function fTiers(strTiers As Variant) As String
Dim TheTier As String
Select Case strTiers
Case Is 1
TheTier = "Greater than 6 months left"
Case = 2
TheTier = "6 months to 3 months"
Case = 3
TheTier = "3 months to 1 month"
Case = 4
TheTier = "1 month left"
Case = 5
TheTier = "expired"
Case Else ' Other values.
TheTier = "Not Tier"
End Select
fTiers = TheTier
End Function
Then in the query field put something like:
Age Date Bracket Desc: fTiers([Age DT CD])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Gary" wrote:
> Looking for help writing two IIF queries that will put some dates into
> brackets. I have some sku's that have a expiration date and the idea is to
> put the product into 1 of 5 brackets. The brackets are:
>
> Brackets:
> Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left",
> = > 181 days
> Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91
> up to 180 days
> Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31
> up to 90 days
> Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30
> Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0
>
> I have the following in my data: Production Date, Shelf Life, and Expiration
> Date. Can someone help me make two fields: Age Date Bracket Code and a Age
> Date Bracket Description?
>
> Test Data is as follows:
> Production DT Shelf Life Expiration DT Age DT CD Age DT DESC
> 12/22/2009 365 12/20/2010 1 New Product
> 10/22/2009 365 10/22/2010 1 New Product
> 10/21/2009 365 10/21/2010 2 Mild, 6 to
> 3 months left
> 06/21/2009 365 06/21/2010 2 Mild, 6 to
> 3 months left
> 06/20/2009 365 06/20/2010 3 Moderat, 3
> to 1 months left
> 05/22/2009 365 05/20/2010 3 Moderat, 3
> to 1 months left
> 05/21/2009 365 05/21/2010 4 Critical, 1
> months left
> 10/24/2009 180 04/22/2010 4 Critical, 1
> months left
> 10/23/2009 180 04/21/2010 5 Expired
>
> Any help create a IIF statement in my query is greatly appreciated.
>
> Regards,
> Gary
|
|
0 |
|
Utf
4/21/2010 6:49:02 PM
On Wed, 21 Apr 2010 10:24:03 -0700, Gary <Gary@discussions.microsoft.com>
wrote:
>Looking for help writing two IIF queries that will put some dates into
>brackets. I have some sku's that have a expiration date and the idea is to
>put the product into 1 of 5 brackets. The brackets are:
>
>Brackets:
>Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left",
>= > 181 days
>Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91
>up to 180 days
>Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31
>up to 90 days
>Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30
>Age Date Bracket Code 5. Age Date Bracket Desc "expired" = <=0
>
>I have the following in my data: Production Date, Shelf Life, and Expiration
>Date. Can someone help me make two fields: Age Date Bracket Code and a Age
>Date Bracket Description?
>
>Test Data is as follows:
>Production DT Shelf Life Expiration DT Age DT CD Age DT DESC
>12/22/2009 365 12/20/2010 1 New Product
>10/22/2009 365 10/22/2010 1 New Product
>10/21/2009 365 10/21/2010 2 Mild, 6 to
>3 months left
>06/21/2009 365 06/21/2010 2 Mild, 6 to
>3 months left
>06/20/2009 365 06/20/2010 3 Moderat, 3
>to 1 months left
>05/22/2009 365 05/20/2010 3 Moderat, 3
>to 1 months left
>05/21/2009 365 05/21/2010 4 Critical, 1
>months left
>10/24/2009 180 04/22/2010 4 Critical, 1
>months left
>10/23/2009 180 04/21/2010 5 Expired
>
>Any help create a IIF statement in my query is greatly appreciated.
>
>Regards,
>Gary
I would suggest that IIF is simply the wrong tool for the job. You could use
VBA as Gary suggests, or - perhaps even better - use a table-driven solution.
Create a table with the days cutoff and the label for values less than that
number of days. You can then use an expression like
DateAdd("d", [Shelf Life], [Production Date])
to calculate the expiration date, and
DateDiff("d", DateAdd("d", [Shelf Life], [Production Date]), Date())
to calculate the number of days until (or past) the expiration. Join this
field to your table of labels to get the appropriate label for today.
--
John W. Vinson [MVP]
|
|
0 |
|
John
4/22/2010 5:15:59 AM
Similar Artilces:
Date function within a query
Hello, I have a query that list individual projects, by project #, and the specific date each was started. I need to add a criteria or field that will only return projects that were started 180 days or 6 months ago. Is there a function that will look at the current minus the start date > 180 days? Below is the SQL: SELECT [tbl Project Detail].[Project Number], [tbl Project Detail].Description, [tbl Project Detail].[Current Month Spending], [tbl Project Detail].[NVL Total CM Budget], [tbl Project Detail].[Total YTD Spending], [tbl Project Detail].[Total TY Budget], [tbl Project Deta...
Mofidied On has the same date/time in many items
Hello, We have noticed that a large number of activities, cases and leads, suddenly has the same date/time value in the SQL Server, in the field "Modified On". Can you tell me how can this happen? Could this happen every time someone goes offline and has the right to sync these items? Is it a CRM bug? I cannot understand how can fields take the same value in a batch way, without human interference... Any help? Thanks, Elena If the records have *exactly* the same time I suspect that someone has likely gonue around the platform and tried to directly update rows in the database. Th...
If statement 01-12-10
I want to look in multiple cells and return a predetermined value: I want cell F5 to return a value if there is an X in any of the cells A1 thru A5, if there is no X but there is an R, I want to return a different value, and if those same cells are empty, I want to return a different value. Is this possible? A B C D E F 1 X 2 R 3 X 4 5 Hi, maybe this =IF(COUNTIF(A1:A5,"X")>0,"XXXXX",IF(COUNTIF(A1:A5,"R")>0,"RRRRR","No X or R")) Mike "gbish" wrote: > I want to look in multiple cells and return a...
advanced filter date criteria
Hi My advanced filter works manually but not in macro > It is a criteria issue as column headings work I am trying to filter data where ship date is <= todays date +28 Columns("g:k").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "m1:m2"), CopyToRange:=Range("p1" _ ), Unique:=False where m1 = ShipDate and m2 = <=15/03/2010 this works manually but not in macro but if replace m2 with a date it works therefore how do i write for less than and equal to Thank you Tina Hi I worked out date has to be mm/dd/yy form...
Formula to get date from text string
This is what the cell currently looks like: [10/01/09 11:30PM] I would like the formula to return only: 10/01/09 Does anyone know what formula I should use? Any help would be greatly appreciated. Thanks!! Sara Here's one way: =3D--MID(A1,2,8) though this will only work if the date is in the normal format for your region (does it mean 10th January 2009, or 1st October 2009 ?). A safer way might be: =3DDATE(2000+MID(A1,8,2),MID(A1,5,2),MID(A1,2,2)) or: =3DDATE(2000+MID(A1,8,2),MID(A1,2,2),MID(A1,5,2)) depending on the answer to my earlier question. ...
Time passed sinec date in column A
I am trying to greate a document that will tell me when someone needs to renew thier training. I have 1, 2, 3, & 4 yearly training programs. I want to have a system where I can set conditional formating to goy amber when it has been 10 months since the date and then Red when over a year> I have a date on column A. In column B I want to know how many months have passed since that date. I am sure the solution is easy, but I can't work it out!! Many thanks Steve Hi Steve, Not a full answer to your question but the following might help to put you on the right track. To find o...
Subtotal by date shows US format rather than other way round
I have a spreadsheet which has data subtotalled by date. My date format is dd/mm/yyyy, but the subtotals show mm/dd/yyyy. When I look in 'format cell' it shows my format is dd/mm/yyy, but even still it shows the other way around. How do I fix this? ...
Printing Direct Deposit Statement of Earnings
I completed the Payroll Direct Deposit setup along with regular Payroll Setup in GP10.0. The process of building payroll (Transaction>Payroll>Build Checks) and calculating payroll (Transaction>Payroll>Calculate Checks) works perfect. But when I go to calculate direct deposit and print earnings statement, the options are not available in the Post Payroll Checks window. FYI, It does allow us to print checks and post checks so that process is working. Did you activate direct deposit on the Direct Deposit Setup window? By default it is inactive. -- Charles Allen, MVP &q...
Excel 2003 - VBA
Guys, Is there a real quick way to simply add 1 month to a date. eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month = Mar. 13, 2008. Craig What if the date is January 30th? =DATE(YEAR(D9),MONTH(D9)+1,DAY(D9)) with your date in D9 -- Regards, Peo Sjoblom "Craig Brandt" <brandtcraig@att.net> wrote in message news:Pvo1k.6233$mh5.1489@nlpi067.nbdc.sbc.com... > Guys, > > Is there a real quick way to simply add 1 month to a date. > eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month = > Mar. > 13, 2008. > &g...
Counting Dates of Occurrences
I am developing a spreadsheet to show the date and time an event has occurred at a location. The cells indicating date and time are custom formated to display mm/dd hh:mm. The first occcrrence is shown in column G, the second occurence in column H, the third iin column I, and so on. This works fine, but I need to count the number of occurrences shown in each column. Can you help? Thanks =COUNT(A2:A200) assuming you are using excel dates and times -- Regards, Peo Sjoblom "wally" <wally@discussions.microsoft.com> wrote in message news:6EEBFCEB-6FAC-40F2-B8B7-04...
Date Function
Today when I open my database appears a message notifying that a not recognized function was on my Form, yesterday it work properly, I referred for some controls the Date() Function and now I had to change it for Now() Function in order to my form works, Do anybody have a clue on what happened??? Im using MS access 2003 and Windows XP Professional,please help!. Thanxs in advance!!. Anytime an application that previously worked stops working, or an application that works on one machine won't work on another machine, the first thing to check is the References collection. (All Access...
extract the month of a date
I want to extract the month of a date. for example I have dat "6/5/2005" which is in "mm/dd/yyyy" format. i want to get the mont part of the date as "Jun". Similarly for the date "8/12/2005" a "Aug". Presently I am doing this with the help of VLOOKUP approach with table having these values. As i am having a huge volume of data to work with a quicker and easie formula can be a great help. Thank you -- girees ----------------------------------------------------------------------- gireesh's Profile: http://www.excelforum.com/member.php?a...
PV, unequal pymts, different dates
Excel 2003. Is ther a formula/template of calculating the Present Value of a series of UNequal payments made at differing dates. Thanks, jcs Look at the XNPV() function. If you get a #NAME error: Tools>Add-ins, check Analysis Toolpak -- Kind regards, Niek Otten Microsoft MVP - Excel "Craig" <jcstone@mindspring.com> wrote in message news:nO2dndH9uodzFRLVnZ2dnUVZ_tHinZ2d@earthlink.com... | Excel 2003. Is ther a formula/template of calculating the Present Value of a | series of UNequal payments made at differing dates. Thanks, jcs | | Thanks, works great. What abou...
numbering records according to date and ID
Hope you can help, I have a groupby query that gets data from several tables called QOrders. The records are sorted by PurchaseDate and then by PurchaseID (Primary Key). I would like to make a running sum in a subform of PurchaseAmount according to the same order (first sort by date then by ID). Since I could only manage to sort by ID even though I entered date as criteria, I wonder if it would be better to add a records counter that will count according to my sorting order. Then I plan to use this counter as criteria for the running sum. My query looks like this PurchaseDate Purcha...
Lost all my Outlook data after a certain date....HELP!
Outlook 2000 has screwed me over big-time! I seem to have lost all my Outlook data for the past 6 months - email, contacts, calendar, etc. All the data prior to 6 mos. ago has been retained. Right before this data loss, I was having strange PC behavior - most likely due to spyware apps that were slowing down performance. I looked at my Outlook PST file and it appears the same size as it was before the data loss, so this missing data should still be in there somewhere? I have tried to export the PST file to different formats, such as Access and tab-separated values, but to no avail. I thi...
wrong date
When I opened money today it lists todays date as 1/21/04 rather than 12/21/03. It nows shows all of my bills as being overdue. Does anyone know how to fix the date? It should use the system date on your computer. Suggest you check that's okay -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to any un...
Formulating Different Start/Finish Dates with Man Hours
I need help with this and hopefully someone can assist! I am working with a spreadsheet that has different start and finish dates during the fiscal year (Oct - Sept). There are man hours associated with each person and their availability. HOW CAN I FORMULATE TO SHOW THE # OF PEOPLE ASSOCIATED & AVAILABLE AS A RESOURCE WITH EACH UNIQUE TIME PERIOD????? Example: Name Start Finish Hrs/Wk 2nd dates hrs/wk Person A 10/1/07 - 9/30/11 40 Person B 10/1/07 - 8/4/08 40 8/4/09 - 9/30/11 40hrs Person C 5/30/09 - 9/30/11 40 Person D 2/11/08 - 8/15/09 20 9/10/10 - 9/30/11 20hrs -- DB I have a co...
count days in a date range
I am trying to count the number of days within a given date range but leave out saturday's and Sunday's. Is there a way to do this? Using the function Days360 seems to be the right path, and counts all the days between the range, but I am not sure how to customize from there, if it is even possible. cp Hi if your dates are in a and B1 use =NETWORKDAYS(A1,B1,list_of_holidays) Note: the Analysis Toolpak Add-in has to be installed for this -- Regards Frank Kabel Frankfurt, Germany cp wrote: > I am trying to count the number of days within a given > date range but leav...
If statements #4
I have a column with various numbers. I want to be able to count certain numbers, such as 0-15,16-30, and so on. I want to be able to write an if statetment that says if a number is between 0 and 15, count it as a 1, 16-30 counted as a 2. Then need to perform a specific count for just 1's, 2s,.... Can you help me Assuming you don't have any negative numbers you could use a countif() =countif(A1:A100,"<=15") would return the count of numbers between 0 - 15 for numbers between 16-30 you could use =countif(A1:A100,"<=30")-countif(A1:A100,"<=15&quo...
Problem with Date Formatting
I am importing a text file with dates in the format 3/1/05. I need the data formatted as DD-MMM-YYYY. I can get this for all dates EXCEPT where the leading zero has been dropped off the day, such as 3/1/05. Can anyone suggest how to do this in Excel? ...
Variables in select statement
Hi, How do you declare variables in an Access 2007 SELECT statement? I want to do something like: Dim PCLine as Integer SET PCLine = 0 SELECT IIf(Col1 = 1, "Cool", PCLine = Col1) AS Example; TIA, Jarryd On Tue, 26 Jan 2010 13:11:43 -0000, "Jarryd" <jarryd@nodomain.com> wrote: SQL Is not appropriate for that; it is to operate on data in your tables. For data and variables in code, use VBA. -Tom. Microsoft Access MVP >Hi, > >How do you declare variables in an Access 2007 SELECT statement? > >I want to do something like:...
email dates missing after import from Outlook Express
I imported my email from Outlook Express to Outlook. All of the messages imported properly, but the view of them is missing the creation date on all over about 6 months old. When I open the mail, the date is available, but only if I open it. What went wrong & how can I get Outlook to display the message date? Thanks! Don S Sorry, the missing date is the "received" date. The view displays "none". It does display the file size. Don S On Sat, 19 Aug 2006 14:47:19 GMT, Don S <donswin+news@gmail.com> wrote: >I imported my email from Outlook Express to Out...
Getting system date
Hello, I was wondering if there was any MFC function which I can call that can get the year, can get the month, and can get the day from the system. Sincerely, James Simpson Straightway Technologies How about: // Convert CTime to FILETIME CTime time(CTime::GetCurrentTime()); SYSTEMTIME timeDest; time.GetAsSystemTime(timeDest); FILETIME fileTime; ::SystemTimeToFileTime(&timeDest, &fileTime); -johnny"James Simpson" <anonymous@discussions.microsoft.com> wrote in message news:03f701c3cba4$6d63fbd0$a301280a@phx.gbl... > Hello, > I was wondering if there was...
Date Differences in Years
Hello, I have the following formula in Access 2007 to determine how many years someone has been with the company. ----- Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) ----- This formula is partially accurate, but not completely because, for example, if someone was hired on 8/24/09 and today's date is 5/27/10, it says that person has worked here for a year (the value result = 1). But because it technically hasn't been a full year, the number isn't accurate. I would like for it to return a value = 0 (zero) if it hasn't been a full y...
Access date datatype import data from Excel
We are trying to import data from an excel spreadsheet into Access. We are having difficulty with a date field from the Excel spreadsheet terminating the import with a generic error message. I have tried several datatypes at each end to make this work with no luck. Can someone help us with this? Thanks. Would please tell us the generic error message you are getting and how your date field is ordered, such as mm/dd/yyyy or other. -- G Vaught "Pegthad" <anonymous@discussions.microsoft.com> wrote in message news:2bf7601c46845$5da0ade0$a301280a@phx.gbl... > We are tr...
Hello, I have a query that list individual projects, by project #, and the specific date each was started. I need to add a criteria or field that will only return projects that were started 180 days or 6 months ago. Is there a function that will look at the current minus the start date > 180 days? Below is the SQL: SELECT [tbl Project Detail].[Project Number], [tbl Project Detail].Description, [tbl Project Detail].[Current Month Spending], [tbl Project Detail].[NVL Total CM Budget], [tbl Project Detail].[Total YTD Spending], [tbl Project Detail].[Total TY Budget], [tbl Project Deta...
Mofidied On has the same date/time in many items
Hello, We have noticed that a large number of activities, cases and leads, suddenly has the same date/time value in the SQL Server, in the field "Modified On". Can you tell me how can this happen? Could this happen every time someone goes offline and has the right to sync these items? Is it a CRM bug? I cannot understand how can fields take the same value in a batch way, without human interference... Any help? Thanks, Elena If the records have *exactly* the same time I suspect that someone has likely gonue around the platform and tried to directly update rows in the database. Th...
If statement 01-12-10
I want to look in multiple cells and return a predetermined value: I want cell F5 to return a value if there is an X in any of the cells A1 thru A5, if there is no X but there is an R, I want to return a different value, and if those same cells are empty, I want to return a different value. Is this possible? A B C D E F 1 X 2 R 3 X 4 5 Hi, maybe this =IF(COUNTIF(A1:A5,"X")>0,"XXXXX",IF(COUNTIF(A1:A5,"R")>0,"RRRRR","No X or R")) Mike "gbish" wrote: > I want to look in multiple cells and return a...
advanced filter date criteria
Hi My advanced filter works manually but not in macro > It is a criteria issue as column headings work I am trying to filter data where ship date is <= todays date +28 Columns("g:k").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "m1:m2"), CopyToRange:=Range("p1" _ ), Unique:=False where m1 = ShipDate and m2 = <=15/03/2010 this works manually but not in macro but if replace m2 with a date it works therefore how do i write for less than and equal to Thank you Tina Hi I worked out date has to be mm/dd/yy form...
Formula to get date from text string
This is what the cell currently looks like: [10/01/09 11:30PM] I would like the formula to return only: 10/01/09 Does anyone know what formula I should use? Any help would be greatly appreciated. Thanks!! Sara Here's one way: =3D--MID(A1,2,8) though this will only work if the date is in the normal format for your region (does it mean 10th January 2009, or 1st October 2009 ?). A safer way might be: =3DDATE(2000+MID(A1,8,2),MID(A1,5,2),MID(A1,2,2)) or: =3DDATE(2000+MID(A1,8,2),MID(A1,2,2),MID(A1,5,2)) depending on the answer to my earlier question. ...
Time passed sinec date in column A
I am trying to greate a document that will tell me when someone needs to renew thier training. I have 1, 2, 3, & 4 yearly training programs. I want to have a system where I can set conditional formating to goy amber when it has been 10 months since the date and then Red when over a year> I have a date on column A. In column B I want to know how many months have passed since that date. I am sure the solution is easy, but I can't work it out!! Many thanks Steve Hi Steve, Not a full answer to your question but the following might help to put you on the right track. To find o...
Subtotal by date shows US format rather than other way round
I have a spreadsheet which has data subtotalled by date. My date format is dd/mm/yyyy, but the subtotals show mm/dd/yyyy. When I look in 'format cell' it shows my format is dd/mm/yyy, but even still it shows the other way around. How do I fix this? ...
Printing Direct Deposit Statement of Earnings
I completed the Payroll Direct Deposit setup along with regular Payroll Setup in GP10.0. The process of building payroll (Transaction>Payroll>Build Checks) and calculating payroll (Transaction>Payroll>Calculate Checks) works perfect. But when I go to calculate direct deposit and print earnings statement, the options are not available in the Post Payroll Checks window. FYI, It does allow us to print checks and post checks so that process is working. Did you activate direct deposit on the Direct Deposit Setup window? By default it is inactive. -- Charles Allen, MVP &q...
Excel 2003 - VBA
Guys, Is there a real quick way to simply add 1 month to a date. eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month = Mar. 13, 2008. Craig What if the date is January 30th? =DATE(YEAR(D9),MONTH(D9)+1,DAY(D9)) with your date in D9 -- Regards, Peo Sjoblom "Craig Brandt" <brandtcraig@att.net> wrote in message news:Pvo1k.6233$mh5.1489@nlpi067.nbdc.sbc.com... > Guys, > > Is there a real quick way to simply add 1 month to a date. > eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month = > Mar. > 13, 2008. > &g...
Counting Dates of Occurrences
I am developing a spreadsheet to show the date and time an event has occurred at a location. The cells indicating date and time are custom formated to display mm/dd hh:mm. The first occcrrence is shown in column G, the second occurence in column H, the third iin column I, and so on. This works fine, but I need to count the number of occurrences shown in each column. Can you help? Thanks =COUNT(A2:A200) assuming you are using excel dates and times -- Regards, Peo Sjoblom "wally" <wally@discussions.microsoft.com> wrote in message news:6EEBFCEB-6FAC-40F2-B8B7-04...
Date Function
Today when I open my database appears a message notifying that a not recognized function was on my Form, yesterday it work properly, I referred for some controls the Date() Function and now I had to change it for Now() Function in order to my form works, Do anybody have a clue on what happened??? Im using MS access 2003 and Windows XP Professional,please help!. Thanxs in advance!!. Anytime an application that previously worked stops working, or an application that works on one machine won't work on another machine, the first thing to check is the References collection. (All Access...
extract the month of a date
I want to extract the month of a date. for example I have dat "6/5/2005" which is in "mm/dd/yyyy" format. i want to get the mont part of the date as "Jun". Similarly for the date "8/12/2005" a "Aug". Presently I am doing this with the help of VLOOKUP approach with table having these values. As i am having a huge volume of data to work with a quicker and easie formula can be a great help. Thank you -- girees ----------------------------------------------------------------------- gireesh's Profile: http://www.excelforum.com/member.php?a...
PV, unequal pymts, different dates
Excel 2003. Is ther a formula/template of calculating the Present Value of a series of UNequal payments made at differing dates. Thanks, jcs Look at the XNPV() function. If you get a #NAME error: Tools>Add-ins, check Analysis Toolpak -- Kind regards, Niek Otten Microsoft MVP - Excel "Craig" <jcstone@mindspring.com> wrote in message news:nO2dndH9uodzFRLVnZ2dnUVZ_tHinZ2d@earthlink.com... | Excel 2003. Is ther a formula/template of calculating the Present Value of a | series of UNequal payments made at differing dates. Thanks, jcs | | Thanks, works great. What abou...
numbering records according to date and ID
Hope you can help, I have a groupby query that gets data from several tables called QOrders. The records are sorted by PurchaseDate and then by PurchaseID (Primary Key). I would like to make a running sum in a subform of PurchaseAmount according to the same order (first sort by date then by ID). Since I could only manage to sort by ID even though I entered date as criteria, I wonder if it would be better to add a records counter that will count according to my sorting order. Then I plan to use this counter as criteria for the running sum. My query looks like this PurchaseDate Purcha...
Lost all my Outlook data after a certain date....HELP!
Outlook 2000 has screwed me over big-time! I seem to have lost all my Outlook data for the past 6 months - email, contacts, calendar, etc. All the data prior to 6 mos. ago has been retained. Right before this data loss, I was having strange PC behavior - most likely due to spyware apps that were slowing down performance. I looked at my Outlook PST file and it appears the same size as it was before the data loss, so this missing data should still be in there somewhere? I have tried to export the PST file to different formats, such as Access and tab-separated values, but to no avail. I thi...
wrong date
When I opened money today it lists todays date as 1/21/04 rather than 12/21/03. It nows shows all of my bills as being overdue. Does anyone know how to fix the date? It should use the system date on your computer. Suggest you check that's okay -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or email mnyukwsh@microsoft.com especially if it's a UK specific wish. I do not respond to any un...
Formulating Different Start/Finish Dates with Man Hours
I need help with this and hopefully someone can assist! I am working with a spreadsheet that has different start and finish dates during the fiscal year (Oct - Sept). There are man hours associated with each person and their availability. HOW CAN I FORMULATE TO SHOW THE # OF PEOPLE ASSOCIATED & AVAILABLE AS A RESOURCE WITH EACH UNIQUE TIME PERIOD????? Example: Name Start Finish Hrs/Wk 2nd dates hrs/wk Person A 10/1/07 - 9/30/11 40 Person B 10/1/07 - 8/4/08 40 8/4/09 - 9/30/11 40hrs Person C 5/30/09 - 9/30/11 40 Person D 2/11/08 - 8/15/09 20 9/10/10 - 9/30/11 20hrs -- DB I have a co...
count days in a date range
I am trying to count the number of days within a given date range but leave out saturday's and Sunday's. Is there a way to do this? Using the function Days360 seems to be the right path, and counts all the days between the range, but I am not sure how to customize from there, if it is even possible. cp Hi if your dates are in a and B1 use =NETWORKDAYS(A1,B1,list_of_holidays) Note: the Analysis Toolpak Add-in has to be installed for this -- Regards Frank Kabel Frankfurt, Germany cp wrote: > I am trying to count the number of days within a given > date range but leav...
If statements #4
I have a column with various numbers. I want to be able to count certain numbers, such as 0-15,16-30, and so on. I want to be able to write an if statetment that says if a number is between 0 and 15, count it as a 1, 16-30 counted as a 2. Then need to perform a specific count for just 1's, 2s,.... Can you help me Assuming you don't have any negative numbers you could use a countif() =countif(A1:A100,"<=15") would return the count of numbers between 0 - 15 for numbers between 16-30 you could use =countif(A1:A100,"<=30")-countif(A1:A100,"<=15&quo...
Problem with Date Formatting
I am importing a text file with dates in the format 3/1/05. I need the data formatted as DD-MMM-YYYY. I can get this for all dates EXCEPT where the leading zero has been dropped off the day, such as 3/1/05. Can anyone suggest how to do this in Excel? ...
Variables in select statement
Hi, How do you declare variables in an Access 2007 SELECT statement? I want to do something like: Dim PCLine as Integer SET PCLine = 0 SELECT IIf(Col1 = 1, "Cool", PCLine = Col1) AS Example; TIA, Jarryd On Tue, 26 Jan 2010 13:11:43 -0000, "Jarryd" <jarryd@nodomain.com> wrote: SQL Is not appropriate for that; it is to operate on data in your tables. For data and variables in code, use VBA. -Tom. Microsoft Access MVP >Hi, > >How do you declare variables in an Access 2007 SELECT statement? > >I want to do something like:...
email dates missing after import from Outlook Express
I imported my email from Outlook Express to Outlook. All of the messages imported properly, but the view of them is missing the creation date on all over about 6 months old. When I open the mail, the date is available, but only if I open it. What went wrong & how can I get Outlook to display the message date? Thanks! Don S Sorry, the missing date is the "received" date. The view displays "none". It does display the file size. Don S On Sat, 19 Aug 2006 14:47:19 GMT, Don S <donswin+news@gmail.com> wrote: >I imported my email from Outlook Express to Out...
Getting system date
Hello, I was wondering if there was any MFC function which I can call that can get the year, can get the month, and can get the day from the system. Sincerely, James Simpson Straightway Technologies How about: // Convert CTime to FILETIME CTime time(CTime::GetCurrentTime()); SYSTEMTIME timeDest; time.GetAsSystemTime(timeDest); FILETIME fileTime; ::SystemTimeToFileTime(&timeDest, &fileTime); -johnny"James Simpson" <anonymous@discussions.microsoft.com> wrote in message news:03f701c3cba4$6d63fbd0$a301280a@phx.gbl... > Hello, > I was wondering if there was...
Date Differences in Years
Hello, I have the following formula in Access 2007 to determine how many years someone has been with the company. ----- Years of Service: DateDiff("yyyy",[Hire Date],[TodaysDate]) ----- This formula is partially accurate, but not completely because, for example, if someone was hired on 8/24/09 and today's date is 5/27/10, it says that person has worked here for a year (the value result = 1). But because it technically hasn't been a full year, the number isn't accurate. I would like for it to return a value = 0 (zero) if it hasn't been a full y...
Access date datatype import data from Excel
We are trying to import data from an excel spreadsheet into Access. We are having difficulty with a date field from the Excel spreadsheet terminating the import with a generic error message. I have tried several datatypes at each end to make this work with no luck. Can someone help us with this? Thanks. Would please tell us the generic error message you are getting and how your date field is ordered, such as mm/dd/yyyy or other. -- G Vaught "Pegthad" <anonymous@discussions.microsoft.com> wrote in message news:2bf7601c46845$5da0ade0$a301280a@phx.gbl... > We are tr...