Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year
did up to intermediate courses in Access.
However, that being said because you guys are very keen in here, so I
dont want to sound stupid, whilst my understanding on alot of excel is
generally on the layman terms!
Heres my problem.
Im making a simple spreadsheet.
I have a widget that enters my facility on one date, it then exits
another. I know by setting up EXIT - ENTER = DAYS I have my duration in
BUT---> I dont want the weekends to count as days!
Material arrives every weekday & exits the...First row in Selection range (first index of a cell)
I have a problem with selecting first cel in selection Range or return
an index of the first cell in Selection Cell.
I have something like this:
"and here I want to Select the first range in selection Range1"
I there any special function of finding first cell in selection range
or returning an index of the first cell??
Thanks for answet
> I have a problem with selecting first cel in selection...Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat
in Excel. Here's my problem:
I have a workbook with 2 worksheets; one is the main data, the other i
the statistics from the main data. Within the main data, there is
column titled "date paid" and another column titled "total paid" (ther
are a bunch more columns, but they don't matter for this problem). Th
"date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O
the stats page, I have columns titled for each month of the year an
would like each to include how much was pa...Export a range to a text file
Hello need some advise on how to procede
I need to be able to create a text file containg some text as well as
data that is within a named range in excel and then some more text. I
can handle printing to the text files using cell values etc but am
unsure of the best way to print the ranges data. Is there a way or
procedure to just print the range as is in csv format? As well my range
will contain about 6 columns, each containg a number field (formatting
of decimal places is important, some have 2 dec some 3 etc) Also the
range has a max of 50 rows however will always contain lower rows of...Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns:
ColID - PRIMARY KEY
Col1 - Number
Col2 - Date/Time
Next I created a multi-field index using Col1 & Col2. I entered the
following row in the 1st row:
Next when I tried to add the above row again, as expected, I wasn't
allowed to enter the same row. Next I entered the following 2 rows:
Both the rows were accepted. After this I deleted the 3 records, went
back to the design view & changed the data type of Col2 from Date/Time
to Number keeping the multi-field index...Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English
to Metric conversion routines in the Access VBA code and would like to run a
query on the data that will return coverted values. I need to be able to
execute this from outside the database (run the query from another program).
Yes. The function must be a Public Function in a standard module. You use a
calculated control to return the results of the query.
Dave Hargis, Microsoft Access MVP
> Can I call a VBA subroutine from within an ...converting dates
I have a couple of excel problems to do with dates (Excel XP/2002).
I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365.
Secondly does anyone know a formula that sorts out leap years (see above).
Any help, suggestions or tutorial links greatly appreciated
As long as you assume 1 year is always 365 days
=INT(A1/365)&" year(s) "&MOD(A1,365)&&q...Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a
text box? And how would I do that?
You would have to have some code to load it, such as
Textbox1.Text = Format(Date,"dd mmm yyyy")
or link the texbox to a cell with the formula =TODAY()
(remove nothere from the email address if mailing direct)
"stephiebrady" <email@example.com> wrote in message
> I know how to display the current date in a cell, but can I display it in
> text box? And ...Button on Form to Run Report based on Query with Parameters
I am busy working on a stock control database (and job control).
When I have a Form Open, it shows several fields, including JobID (Which is
the PK for that job) (frm_Invoicing)
I also have a Query that when Run, it prompts for the JobIDNo.
I have a report based on that query. (rpt_CustJobsInvStock)
I have added a button onto the form (onclick -> DoCmd.OpenReport) to open
and automatically print the report,
But I just cannot figure out how to get it to get my JobID from the form,
and to Automatically add it as a Parameter for the que...Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to
now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " &
DATEDIF(C6,NOW(),"md") & " D"
I'd like a formula that can produce the same format (years, months, days)
between now and a future date.
Thanks in advance,
Try this with the date in A2
=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for
records that came in between two dates. For instance, I would like to be
able to pull all records input in CRM between March 1 and March 15. In 3.0
you can only query specific dates like "Last X days," "Last X Months," "On,"
"On or After," etc., but you cannot search the date fields between two dates.
The functionality was available in 1.2 but is not available in 3.0.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the...Sort search results by date
It would be nice if i could sort my search results in this forum by date.
I would like to see my most recent questions at the top.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroup...Auto insert of date
I'm trying to input some datas using preparedStatement and among those
coiumns one column is of type "smalldatetime" i wish to input the
current date and time at which i execute this query, how can i achieve
this in JDBC or what is the sql query for this.
I'm not sure if we're talking JET SQL or T-SQL or something else here? In
JET SQL you want the Date() function, something like ...
INSERT INTO Table1 ( TestDate )
SELECT Date() AS Expr1;
If you're using T-SQL you want the GETDATE() function, something like ...
INSERT INTO dbo.Table1
(Test...Use specific printer for reports in view mode
I designed a report using printer HP5000 PLC6 and the paper size is A3 in
landscape. When I open the report in my laptop and I want to show the report
in my resentation, it can not showed all contents of the reort, just a hals
is shown, my question is how can we assign this printer to this reprot when I
open it, although I do not need to print it, this is just for my resentation.
My printer default in mypc is lHP desckjet 690c
H. Frank Situmorang
Might try installing the printer driver for the HP5000 PLC6.
Might try suing the printer "Microsoft Office Document Image Writer&qu...Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records
where the Zip codes match). The problem is that in one table, the listing
of zip codes, it is 5 digit zip codes. In the main table some of the zip
codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I
want all the records as long as the 5 digit zip codes matches.
I quess I could delete the - and the last 4 digits on the main table, but it
would be nice to retain the whole zip codes. Is there a way to do this?
to compare to the original ...Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always
calculate the current 5 column range.
Wendy Akers wrote:
> I add a column every week to my work sheet and want the formula to always
> calculate the current 5 column range.
Add a column WHERE?
Where is the "current 5 column range"?
Let's say your data is in range C3:H3. In cell K3, enter the following
array formula (Ctrl+Shift+Enter) to get the average
=AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...how do i subtract one date from another and get an age in years
i am trying to subtract a date of birth from today's date and get an age in
years. Can anyone help me?
Microsoft MVP - Excel
"ucastores" <firstname.lastname@example.org> wrote in message
>i am trying to subtract a date of birth from today's date and get an age in
> years. Can anyone help me?
"ucastores" &l...Queries and reports in access Need help please
Hello everyone ,
I have a little issue to setup a database. I created a table with
approximately 20 columns. The columns have an entry of Yes or no base on a
questionnaire from survey. What can I do to get a result of my table I would
like to know how many "Yes" and "no" I have. I don't know how to add them or
get this information.
Thanks in advance
The first answer to your question is that your table structure is not
really the best for this situation, and it will be a lot easier if you
can set it up differently. Is your database design set in ...calculating dates #2
What formula do i use???
here is my problem... i have 3 columns
A- Qty of days eg: 6, B-start date, eg: 10/12/07 C -end date, , (formula i
am using, (colummn (C) =A+B+1 (copied down the column) this gives me an end
date by adding A and B which i use in a gantt chart, eg; "but" some dates do
not have a start date yet and the end result will read (C) 05/01/00, how can
i get column (C) to remain blank until i put a start date in????
Microsoft MVP - Excel
"spudpeeps" <email@example.com...Formatting the Date in Excel
Is there any way in Excel that I can format the Date so I don't have to type
any slashes between the month and day? In other words, I would like to be
able to type in 0714 press "enter" and have excel recognize that this is
07/14/2005 without typing in 07/14.
Any suggestions would be appreciated.
Short of writing code, you could enter your numerics only date in cell
A1, for instance: 07142005. Then in B1 write a formula:
Then you could copy column B and paste as values into their req...Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL
experience is a bit “trial and error” I don’t even know what to search for
here in the forums that so I can help myself.
I have two tables A and B.
First table has people’s NAMES field.
Second table has a field with people’s ABILITIES but some are repeated:
WORD – Marc Wilson
WORD – Andrea Smart
EXCEL – Francis Junior
COREL – Andrea Smart
COREL – Andrea Smart
I’m trying to build a query to point out the duplicated lines, in this case:
COREL – Andrea Smart
...Date field behavior differs between forms
I have two forms with seemingly identical date fields. In one, if I place my
cursor in the middle of it, the first number I type gets put in the far left
of the field. In the field on the other form, if I place the cursor in the
middle, it starts typing right where I am. Any ideas?
I have thoroughly looked through the properties of each field and each form
and I cannot find what is causing this behavior. I would love to get both of
them to start placing typed characters at the far left instead of where the
Check the Text Alignment property of both controls (not ...union queries
I need to build a query which combine records from a number of similar tables.
Building a union query works great with "normal" records.
Problem is, these tables contain attachments in some fields and MS ACCESS
2007 treat these fields as multi-value fields and does not allow building
APPEND or UNION queries with multi-value fields.
I am so frustrated with this, because I would like to base my searches and
some other queries on this.
On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron
<DawieTheron@discussions.microsoft.com&g...Won't open a specific pdf attachment
Outlook stopped opening a pdf file that I have been receiving on a daily basis for several months. About a week ago I started having propblems opening the pdf file now when I click on the e-mail with the attachment it says it can't open the attachment and then opens the e-mail but there is no attachment. I do not have any problems opening e-mails with other attachments or other e-mails with pdf attachments even from the same vendor. It is just one specific type of file with the same name which comes each day. Any ideas
...2 queries, a date range and a report?!
I'm pretty much a novice access user, and am designing my DB with the
frequent assistance of these forums. However I'm now trying to do something
that I can't find any reference to - Can anyone advise?
I have a report that is to be printed from a form using a print control
button. The report consists of 2 queries which look up 2 different types of
"item" by date range.
I want to be able to prompt the user once for the start and end date. If I
put a start/end request on both of the queries, then the user has to enter
the dates twice, and when you choose...