formula to count matching days between two date ranges (for large list of names)
I have two sheets. Sheet 1 and sheet 2.
Sheet1: has a list of Social Security Numbers, followed by a date
Range (i.e., start date field and end date field). The date ranges are
discrete (i.e., do not overlap) with each other for any given name.
Name A Start Date A End Date
SSN1 1/1/09 1/5/09
SSN2 4/1/09 4/15/09
SSN1 3/2/10 3/15/10
Sheet 2 has the same information (i.e., multiple records with SSNs,
followed by date ranges
Name B Start Date B End Date RESULT NEEDED
SSN1 1/1/09 1/31/09 5
SSN2 4...Advanced Search Folder Criteria
I want to create a search folder that contains compound criteria (search for
x AND y). In the Search Folder Criteria dialog box, on the Advanced tab,
there is a "More Advanced..." button, but it is grayed out. How do I enable
that button, and is there a way to create a search folder with compound
You need to enable the query builder, see this microsoft Knowledgebase
On Dec 17 2008, 3:52=A0pm, "Thomas M." <NoEmailRepl...@Please.com>
> Outlook 2007
&g...excel putting random cells into date format
I opened a new spreadsheetand started inputting data.
About 100 lines into a very simple sheet of only 3
columns the numbers started turning inot dates all by
them selves. I highlighted the cells and reformatted to
the general formatting and it immediately went back to
the date format. Additionally, when I printede the
spreadsheet it printed pages and pages of blank gridlined
sheets until I realized it and turned off the printer.
THen when I saved the file it was 7meg! This file had 5
pages, 3 colums each and no more than 300 rows each. I
cut and pasted the data into a spreadsh...Formula reads date as number
I have a formula that reads a list of dates in cells K5 through K20 an
compares these dates to 1 date in cell M3. Cell M3 shows a date bu
when I run the evaluate formula auditing tool on the formula, M3 date
shows as a number, thus the formula fails.
pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=49423
Dates are stored as number of days since 1-1-1900. So today (dec 16,2005) is
stored as...Consolidation if a least one criteria is met
I have the follwoing
Name Value Criteria
Aby 7 #N/A
Oby 9 #N/A
Ubi 1 #N/A
Orbi 9 Yes
Aby 6 Yes
Oby 2 Yes
and I would like to consolidate the data if "yes" is present at least
once for the name
e.g. Aby = 13, Oby = 11, Orbi = 9.
I tried this formula in column D
=SUMIFS(B2:B6,A2:A6,A2,C2:C6,OR(C2:C6="Yes",C2:C6)) and remove the
duplicates but the formula does not add value with criteria = "Yes"
Look at this:
=IF(COUNTIFS($C$2:$C$7,"Y...Sum cells using criteria from a row and a column
I am trying to sum cells in a sheet based on matching criteria in a row and
matching criteria in a column.
The data that I am working with is represented as:
Resource: Resource1 JAN JAN JAN JAN FEB
Project Project Description 12/19 12/26 1/2 1/9 1/16
Admin Administration 10 10 10
Holiday Holiday 8
Other Other time Off
Training Training 20
Vacation Vacation 22 30 10 30 22
I ...in access databse the function date() returns #name
i have created an access data base at access 2003 profisonal and used the function date() it worked properly . but when i have coped my data base at another computer at the same office , the funtion retuned (name#) aW4gQWNjZXNzOyB0aGUgRGF0ZSBmdW5jdGlvbiBkb2Vzbid0IHdvcmsgc29tZXRpbWVzLgoKaXQgd29ya3MgYWxsIHRoZSB0aW1lIGluIFNRTCBTZXJ2ZXI7IGJ1dCB5b3UndmUgZ290IHRvIGNhbGwgaXQKR0VUREFURSgpCgoKCk9uIE1hciA3LCAxMToyNMKgcG0sINmF2K3ZhdivINiq2KfYrCDYp9mE2KPYtdmB2YrYp9ihINin2YTYqNiu2YrYqiDZhdit2YXYryDYp9mE2LPZitivCjxAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGU6Cj4gaSBoYXZlIGNyZWF0ZWQgYW4gYWNjZXNzIGRhdGEgYmFzZ...Converting Date to Work Week...
I'm trying to convert a colum of entry dates to work weeks. I have a
helper row for month and year but I do not know if there is a quick way
(or a built in formula in excel) to determine the "work week" (within a
ENTRY DATE: Month: Work Week:
8/26/05 August 4
8/19/05 August 3
Is there a formula that I can drag down this list of about 1,800 rows
with all different months?
PokerZan's Profile: ...Date and time calculations
I have a problem with a formula in wich I like to add up an amount of
hours to a date/time value.
Please find below an example of what I am trying to do.
Value A1: 15/03/2005 22:00 (dd/mm/yyyy hh:mm)
Value A2: 5 (hours)
In cel A3 I would like to get the value of A1+A2 (result 16/03/2005
Can someone suggest what formula I must use?
Thanx very much!
Format the way you like. (5 hours is 5/24ths of a day.)
> I have a problem with a formula in wich I like to add up an amount of
> hours to a date/time value.
>...Persisting kernel driver parameters
Can anyone tell me what is the method of persisting data for kernel
Is it the registry? Is it based on normal files?
Use the registry, unless you are talkig very big data files, putting it in
the registry under the driver's services key beneath a Parameters key is the
Don Burn (MVP, Windows DKD)
Windows Filesystem and Driver Consulting
"sinosoidal" <email@example.com> wrote in message
news:A45EB006-372...Crystal XI will not show all possible values in a parameter field
We have a Crystal XI report that has one parameter value that is a "static"
rather than "dynamic". It works fine and, when you type in a value upon
refreshing the report, the data is correct.
We made a copy of the report, because we want a second report where the
parameter value is dynamic rather than static. We do not want a range, but
we do want a parameter that will show all possible values on the left and
allow you to pick and choose one or more of those values.
For some reason, not all of the possible values are showing in this dynamic
parameter field...How attach data in a row so it can be sorted by the date column?
I have a four spread sheets all the same that I have copied and pasted into
one. I need to know now how to sort the rows by the date column. When I
select the date column and hit the tool bar a-z button only the dates sort
and not other information in the adjacent columns. What can I do?
Select the entire range that you want to sort.
Sort by the column that contain the dates.
>I have a four spread sheets all the same that I have
copied and pasted into
>one. I need to know now how to sort the rows by the date
column. W...Possible to chart data for dates implicit within a range?
I have a list of computer programs with start and end dates for each. I need
to determine how many programs were running on my system on any given day.
Can I get this data for individual days between the start and end dates
without creating columns for each intermediate day? Ex.: Program 1 ran from
01/01/2005 to 01/14/2005. Program 2 ran from 01/06/2005 to 01/22/2005. Is it
possible to chart the total number of programs running on 01/08/2005 without
manually creating a column for that date?
You can compute this with formulas. Star with data in A1:C4 as follows:
...Difference criteria from different forms
I have messed with this for a while now and can get it to work. I have two
different tabs (in a tab control) that have basically the same combo box
which is a "Job#". There is a report button next to each of the combo box's
that will run the same report. So if someone chose Job# ABC in tab 1 then
navigated over to tab 2 and chose Job # 123 then clicks the report button on
next to job# 123 the query criteria for "Job#" should use 123. however if
they went back to tab 1 and hit the report button next to Job ABC then the
query criteria for job# should use ABC.
S...Grouping dates on a chart
I have a workbook with 12 sheets in it, one for each month of the
year. Each sheet has the day of the week in column B, several other
values in columns C -- J and a transaction value in column K. What I
want to do is create a chart that groups the dates into days of the
week and then displays a bar showing the sum of all transactions on
each of those days in the month. So, for example, the sheet for
1 x x x x x x x x 1250.00
2 x x x x x x x x 80.00
3 x x x x x x x x 3000.00
4 x x x x x x x...Can I set date reminders in MS Excel?
I need to have date reminders in MS Excel. Is there a way out?
what do you mean by a date reminder? Do you mean, like, how many days
left till a project is do?? or maybe like a a schedule program to remind you
of an appoinment???
> I need to have date reminders in MS Excel. Is there a way out?
I read this reply message and I would like to know the same thing. A message
to pop up if a project is due in a day.
> what do you mean by a date reminder? Do you mean, like, how many days
> left till a project ...multiple criteria selection
i have a query with ten different fields. all fields contain vehicles, e.g.
alfa romeo,fiat,chrysler,etc. i have created a form with check boxes for each
i aim for my query to return data according to the check boxes (usually more
than one check box is selected) if the check box values are in any of the ten
fields of my query.
can i achieve that with only one query and if yes how? or do i have to use
more than one query to filter my data?
On Mon, 28 Jan 2008 11:18:03 -0800, angie <firstname.lastname@example.org>
>i have a query with ten different fields. all...date and time formatting
I have a formula in which the date and time are present in cells, bu
when the destination cell returns them, they are in their numerica
value. I need them to appear in the destination cell the same way the
do in their home cells.
here is the formula:
="p-v-w-"&B154&" ph "&E154&" fax "&H154&" ** "&X154&" * "&Y154&"
"&AA154&" * "&" * "&AB154&" "&(Y4)&" - "&(Y5)
y4 and y5 are the date and time cell refs
Thanks and Happy ...Partial match for query criteria
I am trying to create a query that will deliver results based on matching
only a part of the text in a table field. For example, if I have a table that
contains the names, address and phone numbers of 10,000 businesses but I only
want my query to show me businesses that have the word "National" in their
name, how would I do that? I'm not sure how to set up the criteria.
Thanks for your help!
Use the Like operator with wildcards. In the 'criteria' row of the business
name column in query design view enter:
Or you can use a ...Date Code
Here is my code that I'm having a problem with. I want the programming to
start evaluating from cell I2 through the rest of the column. Is there a
different way to write the code. Well the workbook opens, I get a message
that says 'Type Mismatch' and highlights the boolean section of the if
Private Sub Workbook_Open()
Dim Target As Range
For Each Target In Range("I2:I20")
If Target.Value <> "" And DateValue(Target.Value) - DateValue(Now)...calculate dates in Excel 2000
I have a spreadsheet with two date columns, a holiday start date and a
holiday finish date. I need to calculate the amount of holidays taken but if
I subtract one from the other, I get the wrong answer. Eg 01/01/05 and
10/01/05, would be 10 days taken, however the formula returns only 9. Any
Look at the NETWORKDAYS() function: will that do it for you? Your Jan
1 through Jan 10 example includes two weekends, so NETWORKDAYS will not
be your best choice if you count weekend days as holidays.
If you say "I'll be on holiday from the 7th through the 10th&...Inser the current date into a field in a Table
Hi, I want to insert the current date into "LastUpdateCost" field fromthe "CYCLETICKETNUMBER" table. I have the following code but is notcomplete. Can you help me????? strSql = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) Date ;" db.Execute strSql, dbFailOnErrorThe code is incomplete because I don't know the what code I need to dowhat I want. Thanks for your help. Hope you have a nice day.JC email@example.com wrote:> Hi, I want to insert the current date into "LastUpdateCost" field from> the "CYCLETICKETNUMBER" table. I h...word document date
Operating System: Mac OS X 10.6 (Snow Leopard)
I'm trying to create an invoice for last month + I cannot stop word from automatically inserting today's date. This occurs in a template I downloaded from microsoft.
Go to Insert menu choose Date and time choose desired format and make sure that automatically update is unchecked.
> Go to Insert menu choose Date and time choose desired format and make sure that automatically update is unchecked. <br><br>Thank you.
...Creating a Report with Multi Criteria
I would like to create a report that will allow for multi criteria. More
than one Parameter. For example, I want to print a report for more than one
company name. Have it come up as a list box and then allow them to
mulit-select the companies I want the report to print. I was able to create a
list box allowing for multi-criteria select but how do I get it to a report?
You need to step through the list box's ItemsSelected collection and build a
value list which can be used to filter the report. Set up the list box like
For its RowSource property:
SELECT CompanyID...Why is date accessed and date modified different?(Excel 2000)
I am puzzled by the fact that one of my excel documents shows a different
modified time than the the accessed time. I do not remember saving(i wrote
down the last time I saved and the time modified) and this time is changed. I
don't share computers with anyone. Does the system modify these files
Some functions such as NOW and TODAY are "volatile" meaning that they will
cause a calculation of those cells when any calculation is mode, including
updating those values when the workbook is open.
Microsoft Most Valuable Profe...