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?
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.
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
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.
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.
A1 contains beginning date of employment B1 contains ending date of employment C1 contains specified beginning date (criteria) D1 contains specified ending date (criteria)
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
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.
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:
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.
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.
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,
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.
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
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".
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
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 ....
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.
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
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?
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.
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
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?
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!
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?
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???
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):
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.
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.
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.
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
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
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.