Date function within a query
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
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
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
=IF(COUNTIF(A1:A5,"X")>0,"XXXXX",IF(COUNTIF(A1:A5,"R")>0,"RRRRR","No X or R"))
> I want to look in multiple cells and return a...advanced filter date criteria
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" _
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
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:
I would like the formula to return only: 10/01/09
Does anyone know what formula I should use? Any help would be greatly
Here's one way:
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:
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
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!!
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
Did you activate direct deposit on the Direct Deposit Setup window? By
default it is inactive.
Charles Allen, MVP
&q...Excel 2003 - VBA
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.
What if the date is January 30th?
with your date in D9
"Craig Brandt" <email@example.com> wrote in message
> 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 =
> 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
assuming you are using excel dates and times
"wally" <firstname.lastname@example.org> wrote in message
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
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.
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
Microsoft MVP - Excel
"Craig" <email@example.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
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
Glyn Simpson, Microsoft MVP - Money
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 firstname.lastname@example.org 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?????
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
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.
if your dates are in a and B1 use
Note: the Analysis Toolpak Add-in has to be installed for this
> 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
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;
On Tue, 26 Jan 2010 13:11:43 -0000, "Jarryd" <email@example.com>
SQL Is not appropriate for that; it is to operate on data in your
tables. For data and variables in code, use VBA.
Microsoft Access MVP
>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?
Sorry, the missing date is the "received" date. The view displays
"none". It does display the file size.
On Sat, 19 Aug 2006 14:47:19 GMT, Don S <firstname.lastname@example.org>
>I imported my email from Outlook Express to Out...Getting system date
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.
// Convert CTime to FILETIME
::SystemTimeToFileTime(&timeDest, &fileTime); -johnny"James Simpson"
<email@example.com> wrote in message
> I was wondering if there was...Date Differences in Years
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?
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.
"Pegthad" <firstname.lastname@example.org> wrote in message
> We are tr...