Find Corresponding Date Range Based On Single Date And ID Number
Mar 28, 2014
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
View 13 Replies
ADVERTISEMENT
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
Feb 11, 2013
How to find out the number of cells in a range containing dates less than a specified date.
View 4 Replies
View Related
Aug 13, 2009
1 column in my sheet is a list of dates (affectively a calendar). I am trying to create a macro that will only display rows 1-5 and then current the current date and the next 30 days and hide all other rows.
The approach I have tried is to use “ADDRESS(MATCH(BS40,$B$1:$B$462,0),2)” equation to find the cell with todays date in it (Cell BS40 contains todays date). This was repeated for todays date +30. The problem I have is that although I now have the cell address’s located I cannot reference their values in a range.select
What I want to do is somwthing like this:
View 2 Replies
View Related
Jul 13, 2014
I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.
View 6 Replies
View Related
May 14, 2012
I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.
follow the link to view the excel file.
http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL
[code].....
View 8 Replies
View Related
Aug 10, 2014
I am looking to get a formula based on my spreadsheet attached
I want F collumn to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Collumn next to the date that has been input?
View 1 Replies
View Related
Aug 14, 2014
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx‎
View 5 Replies
View Related
Dec 12, 2011
way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:
A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01
I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?
I want to do this without using any macros.
View 9 Replies
View Related
Apr 23, 2008
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008
View 4 Replies
View Related
Mar 17, 2008
Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.
View 4 Replies
View Related
Jul 30, 2007
This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up.
Private Sub CommandButton7_Click()
On Error Goto errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End
'startDate = Format(startDate, "mm/??/yy")
'stopDate = Format(stopDate, "mm/??/yy")
startRow = Me.Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Me.Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Me. Range("A" & startRow & ":A" & stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub
View 9 Replies
View Related
Mar 5, 2014
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
View 1 Replies
View Related
Jul 1, 2008
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View 2 Replies
View Related
Mar 30, 2007
I would like to take a date and reduce it to single number. Eg.
Reduce the day to a number. Reduce the month to a number. Reduce the year to a number.
Add the 3 together. This gives you a number.
Reduce this number to a number.
e.g. today's number date 30 / 03 / 2007
Day is 30
Month is 3
Year = 9
Sum of these is 15 which reduces to 6.
3+0+0+3+2+0+0+7=15
1+5=6
Basically 9 is the highest number. The number 13 would be 1+3=4 and so on. The idea is to return a single number between 1-9.
View 14 Replies
View Related
Jun 20, 2013
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
View 12 Replies
View Related
Jul 31, 2009
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
View 9 Replies
View Related
Dec 31, 2009
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
View 8 Replies
View Related
Dec 28, 2011
I have a column of over 20,000 rows, showing employee hire dates. For the purposes of a specific calculation, I want to replace all the hire dates that are prior to 1/1/2011 with 1/1/2011. Is there a simple way to do this all at once with a Replace statement in my code without having to look at each record individually?
I know how to replace one specific date with another all at once, but I don't know to do it when I'm looking for more than one specific date.
View 6 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Mar 9, 2014
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
View 4 Replies
View Related
Jul 11, 2009
I am trying to put a 1 in a cell whenever you find a unique number and date. If you find the same number and date combination, ignore it after the first one. I have attached a file that better explains the problem.
View 2 Replies
View Related
Apr 22, 2009
In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)
The Week ( Monday to sunday) which need to be calculated is the week no. in the given month
like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april
View 14 Replies
View Related
May 26, 2013
[URL]
how to find the max date of a value in column A. From there I need to link up the price associated with that date. VBA is preferred. The information I'm working with is structured like this in this image:
MaxDate.jpg
What I want to do using VBA, is to look at the values in column A and for each of them, find the max date or most recent date. Once that is determined, on a new sheet, I want to write the value found in column A in sheet2/ColumnA and the date in column B, and the price in column C. In a nutshell, I'm looking for an effective date for pricing. The example on the bottom is basically what I'd like to extract with code onto another sheet.
View 6 Replies
View Related
Sep 8, 2009
I have a table that shows across the top row date headings for year and month. So it would show like this, 2008/01 2008/02 2008/03 2008/04 etc to year ending 2009/12
I need a formula which will tell me the first date that a customer started transacting. Under each date heading is a count of transactions for each year/month by each customer. So, I need to search for the very first transaction number and return the date.
View 2 Replies
View Related
May 19, 2006
if I have 2 dates 21/05/2006 and 23/05/2006, how can I find out if 22/05/2006 exists between them
View 4 Replies
View Related
Apr 11, 2007
I have a formula =WORKDAY(TODAY(),-1) which will give me 10 Apr 07 in C1of sheet1 today. I am trying to find the cell in column A of sheet2 which has the date value equal to C1. However, my below macro keeps telling me object variable not set at the last line.
Dim s1, s2 As worksheet
Dim tdy As range
Dim today As Long
Set s1 = sheets("Sheet1")
Set s2 = sheets("Sheet2")
today = s1.range("C1")
Set tdy = s2.range("A1:A65536").find(today, lookin:=xlvalues)
tdy.Activate
View 9 Replies
View Related
Oct 8, 2013
I have a spreadsheet that contains dates in format mm/dd/yyyy. Lets call this Column C(Paydate). In Column A I have a date that needs to be a 2 business days before the paydate. If this date in column A winds up being a Saturday or Sunday, the function needs to return the previous Friday. If the date is any weekday then it should return that weekday
For example:
C1 = 10/18/2013(a Friday) then A1=10/16/2013
C2 = 10/14/2013(a Monday) then A2= 10/11/2013(the previous Friday since 2 days before 10/14/2013 is a Saturday)
C3 = 10/15/2013(a Tuesday) then A3= 10/11/2013(the previous Friday since 2 days before 10/15/2013 is a Sunday)
View 4 Replies
View Related
Apr 17, 2014
I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :
1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number
I've seen a solution using the Median function but I'm not here it would work in my case. link here
I've attached a workbook : Date_Sample.xlsx‎
View 12 Replies
View Related
Mar 20, 2012
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
View 1 Replies
View Related