Determining Date Ranges
Mar 31, 2009
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?
View 4 Replies
ADVERTISEMENT
Dec 15, 2008
I am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
View 2 Replies
View Related
Apr 21, 2006
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
View 4 Replies
View Related
Jul 27, 2012
ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed
View 2 Replies
View Related
Sep 14, 2008
Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies
View Related
Mar 9, 2013
So i have have a two week period that is established by two dates (a start date "F3"... And an end date "F4")
I have a formula that determine if the "1st" of the month falls within the 13 days preceding the start day. This will notify me that I can the deposit my tenant first check on the start date.
Now that that is known, i would like to create a formula that will establish a second date in that month that will notify me when I can deposit my other renters payment. It will always be two after i deposit the first renters cheque.
View 2 Replies
View Related
Sep 25, 2013
I have been using this statement
if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))
And it just works for the first 2 values c1, c2 and doesn't fit for the others.
The case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.
Here are some samples:
Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02
So as you can see there are more than one event between one start and end dtimes.
View 4 Replies
View Related
Sep 25, 2013
I have been using this statement.
if(and(c1>=a1:a144,c1
View 3 Replies
View Related
Oct 11, 2008
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
View 2 Replies
View Related
Nov 8, 2012
I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
View 9 Replies
View Related
Jan 5, 2012
I have a table with three columns. I'm building a calendar on a separate worksheet and am looking up the "value" based on a calendar date. So if a date falls within any of the ranges, I'd like to return the value in column C. For example, if the date is 02/07/12, I'd like for the result to be value 1, or if the date is 04/17/12, then I would like the result to be value 3. I've used a nested vlookup, but all that give me is the value when either the start or end dates match, but I can't get a value when the date falls within the range. If the dates were consecutive, I would simply use vlookup/TRUE, but the dates are not consecutive.
ABC102/06/1202/09/12value 1203/12/1203/15/12value 2304/16/1204/19/12value 3405/21/1205/24/12value 4506/25/1206/28/12value 5606/25/1206/28/12value 6
View 4 Replies
View Related
Mar 1, 2007
I want to have a new macro/button to redo the chart cells based on the dates in cols T and U (abt 400 entries). T has the start date and U has the end date.
Row 9 - X9: CJ9 have dates for about 10 months (weekdays only)
Column J have either of these two values: A or B
Column C has either of these two values: C or blank
I would envisage it going through each row, checking:
If date at top of that col was between the dates in cols T & U
then
if col J = 'A' then set cell to black block
else if col J = 'B'
then
if col C = C (closed) then set cell to red block
else set cell to blue block
View 2 Replies
View Related
Dec 4, 2012
[URL]
take a look at the attached example and let me know how to make the formula work?
For each "title" in column A, I want to compare the two sets of start and end dates (columns B & C versus D & E) and if the date range in column B &C overlaps with the date range in column D & E, input "yes" or "no" in column F.
In my attached example, rows 8,9,11, and 12 would be populated with a "yes", the rest would be "no".
View 3 Replies
View Related
Dec 13, 2012
Sum based on date ranges. I am creating one logical formula.
I have an excel sheet with two columns.
A1: Date B1: Time Spent
Date
Time spent (in Minutes)
Total
[Code].....
As per above figure I want to sum based on the same dates. I.e A2 has date 1/26/2012 for which first entry is 12 so total will say 12 in C1, for the same date second entry is 14 so 12+14=26 is there in total at C2, for the third and last entry of 1/26/2012 it's 16 so total would be 12+14+16 = 42 in C3 which is grand total for 1/26/2012. I want to fill entire C column with some logical formula so that the logic holds true for various dates.
I have tried to use the IF formula like =IF(A1=A2,SUM(B1:B2)) but it fails at third cell. I have tried several ways to do this but no luck so far ....
View 2 Replies
View Related
Oct 28, 2008
I have start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
View 12 Replies
View Related
May 25, 2006
I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?
View 10 Replies
View Related
Nov 24, 2008
There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.
So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.
I'll give a small example to illustrate what I mean.
I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:
Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008
As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?
Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.
View 14 Replies
View Related
Jun 8, 2009
I would like to get a monthly average between the first of the month & the end of month of a handful of percentages collected during the month for individual projects.
View 10 Replies
View Related
Sep 17, 2012
I'm trying to recreate a formula in VBA, but struggling with the "greater than /equal to start date" and "less than / equal to end date" parts
this formula works ok in the spreadsheet:
Code:
=SUMIFS(bbHours,empCodeList,thisEmployeeID,bbDate,">="&startDate,bbDate,"=" & startDate, Range("bbDate"), "=" section, but unclear how to handle this. It must be handled entirely via VBA, not formulas
Note: I've adjusted certain key names / VBA strings for clarity, everything is defined correctly
View 1 Replies
View Related
Jul 2, 2008
What I'm trying to do is sum the daily interest of a loan for any range of dates. Imagine this example:
1) In column A you have dates from 01/01/2009 through 12/31/2059, with each individual day showing, so literally thousands of cells.
2) In column B you have just random values corresponding to each date, such as $100,000 on 01/01/2009, $139,000 on 01/02/2009, $52 on 01/03/2009, and so forth. These values occupy as many cells as the dates, i.e. there's a cash flow (value) for every date.
3) I want to run a formula in column C that will sum a date range I specify. For example, if I want to sum all the daily values for each month (i.e. the 1st through the 30th), or if I want to sum all the daily values for the 15th of a month through the 14th of the following month. In other words, the loan can start on any date, and I need to sum the interest for each "monthly anniversary."
4) I'm thinking that the formula will have to be different to sum an actual entire month. For example, formulas to sum from, say, the 15th of one month through the 14th of the next month, or the 10th of one month through the 9th of the next month, might be similar.
However, as there is a varying number of days in most months, that formula might not work (though I might be misunderstanding myself) when trying to sum the values for an entire month, i.e. February 1st through 28th. I think it might be solved by some sort of "if," such as if day = 1, then sum whole month, otherwise some from date through same date of next month less the last date's cash flows?
View 9 Replies
View Related
Feb 18, 2009
I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!
View 9 Replies
View Related
Apr 14, 2009
I an having trouble with DSUM, the result I get is 0. I have a Journal Entry and I need to total the amount for a particular range date.
For the header I have:
Date|Account|Debit|Credit|Total|Account Name|Description
This is located from A5:G21
For A1:C2 I have Account|Date Start|Date End
For both Date I used the formula =">="&TEXT(E2,"mm/dd/yyyy") and the same for the ending date which is ="
View 9 Replies
View Related
Oct 27, 2009
i had some formula provided by this site which seemed to work but is now resulting in #N/A or a zero value - can you take a look?
=SUMPRODUCT(('Report X'!$J$6:$J$10000="2")*('Report X'!$E$6:$E$10000>TODAY()-30))
Report X contains 2 relevant columns... E contains the date created for each item, J contains the type of each item - in this case i wish to see how many items '2' there are in the last 30 days
Report X is a daily report copied into the same tab each day - so the data will change every day depending how long each item is still in the system
this formula does work in the example sheet i provided but not in the spreadsheet in which i need it to work!
i also have similar formulas to calculate how many items are present in between 30 and 60 days exist, and how many over 60 days - all return the value "0"
column E containing dates is in the format 01/10/2008 - changed using Ctrl F - replace to amend it from 01.10.2008
View 9 Replies
View Related
Sep 19, 2007
in column A, i have a dates listed, in column G, the transaction amounts that correspond with those dates, i want to know how to run a report on this. what i need is to auto sum the amounts for a specific month without having to create a new spreadsheet for every month. IE - what are the sales for september? how do i do this?
View 2 Replies
View Related
May 19, 2013
I am trying to work on a spreadsheet (I am very new to all this). I have colum I with dates in and Colum K with dates in... When a product comes into my shop I enter the date it arrived in Colum I and when it leaves I enter the date in Colum K.
What I would like to try to work out is how do I ask Excel to tell me How many blank cells there are in Colum K if I enter a date range for Colum I
I will try to give an example.
Colum K may have 200 entries for the week 6/5/13 to 10/5/13. I need to know how many cells are blank in Colum K if the date range on Colum I is between 6/5/13 to 10/5/13
The end result should basically tell me how much stock I have left in my shop for that week.
I have found something that sort of works by using this =SUMPRODUCT((I1:I200<=TODAY())*(K1:K200="")) but ideally I need to be able to change the word TODAY into a date range like 6/5/13 to 10/5/13 and I cant seem to do that???
View 5 Replies
View Related
Dec 9, 2013
I have an INDIRECT function set up which should, in theory, concatenate data from a date range listed in another cell to correctly reference an external spreadsheet, which is named based on the date range it is generated for. The issue I'm running into is that the program which generates the external spreadsheet uses an MMDDYYYY format when naming its output file. This works fine if the month and day are both 10 or greater, but if they are less than 10, it doesn't work - for example, my INDIRECT function would return the date 5/3/13 as 532013, whereas the file name would be 05032013. I was contemplating using a series of IF statements to fix this, but there has to be a simpler way. Here are the relevant functions.
Function which generates date range (later used in INDIRECT function):
=MONTH(MIN(TransData!A2:A9882))&"/"&DAY(MIN(TransData!A2:A9882))&"/"&YEAR(MIN(TransData!A2:A9882))&" - "&MONTH(MAX(TransData!A2:A9882))&"/"&DAY(MAX(TransData!A2:A9882))&"/"&YEAR(MAX(TransData!A2:A9882))
TransData is a different sheet within the same workbook where the various dates are stored in column A. I found that I needed to format the formula like this in order to get it to return actual dates instead of date sequence numbers.
=AVERAGE(INDIRECT("'[DCQA_"&SUBSTITUTE(B4,"/","")&".xlsm]Total'!$C$3:$C$46"))
Cell B4 is where the date range formula listed above lives. "DCQA_" is the beginning of the filename of the external sheet, followed by the date range in MMDDYYYY - MMDDYYYY format. So, the sheet for 12/2/13 to 12/7/13 would be named DCQA_12022013 - 12072013. I'm unfortunately unable to change the way the external program names its output sheets, so I have to fix this from within my workbook.
View 2 Replies
View Related
Jun 17, 2014
I am new with Macros. How we can automate the attached file?
Every time we enter a Start Date and End Date, the right side section should be updated as per the newly entered dates.
View 1 Replies
View Related
Mar 15, 2012
I would like to utilize the IF function to show the following:
If the date in cell J11 is before April 30, J19 will show J18 * .01, if the date in cell J11 is between May 1 and May 30, J10 will show J18 * .02, and if the date in cell J11 is between June 1 and June 30, J10 will show J18 * .03.
View 3 Replies
View Related
Oct 8, 2012
I have a spreadsheet where I have a list of patients who were evaluated on a certain date unique to that patient. Some of the patients have a subsequent date of death, and some do not (they're still alive).
I need to create a logical test which indicates whether or not the patient died at specific intervals after their unique evaluation date-- at 3 months, 6 months, and 12 months. Additionally, I need to measure whether the patient died at 0-6 months, or 0-12 months, or is still alive.
In other words, if Patient A was evaluated on 1/1/2008 and subsequently died on 10/31/2008, I need to do a logical test that says 3 months = false (no death), 6 months = false (no death) and 12 months = true (death). True = 1 and False = 0 (so I can do a simple column add to figure out how many died at each interval). Additionally, for the 0-6 month test it should calculate = 0, but for the 0 - 12 month test it should calculate = 1.
I'm totally stumped as to how to do this. Currently my spreadsheet reads as follows: (I've put ** next to cells that I need formulas to calculate. If no **, assume it's a value I've inputted manually)
A1 = Patient number (numerical order)
B1 = Patient Initials
C1 = Initial Evaluation date
D1 = Date of death (if any- blank if patient is still alive)
E1 = Patient death indicator (1 = dead, 0 = alive)
F1** = Indicate whether or not patient died within 3 months of evaluation (1 = dead, 0 = alive)
G1** = Actual date of death within 3 month interval
H1** = Indicate whether or not patient died within 6 months of evaluation (1 = dead, 0 = alive)
I1** = Actual date of death within 6 month interval
J1** = Indicate whether or not patient died within 12 months of evaluation(1 = dead, 0 = alive)
K1** = Actual date of death within 12 month interval
L1** = Did patient die within 0-6 months of evaluation? (1 = dead, 0 = alive)
M1** = Actual date of death within 0-6 months
N1** = Did patient die within 0-12 months of evaluation? (1 = dead, 0 = alive)
O1** = Actual date of death within 0-12 months
View 7 Replies
View Related
Feb 5, 2014
I have 2 tables where I need to pull data from one table into the other.
Table 1 - pull data here
A
Site
B
Begin Date
C
End Date
Rate
[Code]...
For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.
Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.
View 1 Replies
View Related
