Compare Dates To Date Range & Return Date Based On Outcome
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
ADVERTISEMENT
Jun 2, 2014
I would like to know the function/syntax for determining the due dates given certain date ranges.
See attached sheet for the example : due dates example.xlsx‎
View 5 Replies
View Related
Feb 28, 2008
I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.
In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.
View 4 Replies
View Related
Feb 13, 2014
I am attempting to find a way to search through a database I created based upon every day of the year. My goal is to have a user input a beginning date and end date for a trip, then my functions will return the range specified with each day and it's individual temperature average (in my database).
I also need generating the dates between the start and end dates that are entered by the user. I was considering combining Days360 with an Hlookup that searches through a massive sheet with all the dates for the upcoming year and then going down 1 in the column for the difference in the dates in each column.
Vlookup Help.xlsx
View 5 Replies
View Related
Jan 18, 2007
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))
Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")
Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)
'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................
View 2 Replies
View Related
Mar 6, 2013
I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.
So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.
In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.
View 12 Replies
View Related
Nov 24, 2007
I have a column of dates (B2:B1500) that I want to compare to a single date (A1).
If the dates in Column B are older than the date in A1 I want to delete the entire row.
View 3 Replies
View Related
May 21, 2008
I am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.
Is As follows
Dim rowNum As Integer, colNum As Integer, currCell As Range
rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If
View 4 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
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
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
May 30, 2014
What I want to do is return the oldest date to another sheet, but I only want to return the oldest date if the backlog value is higher than 0. So example below has oldest date with 0 as backlog value, I don't want to return that date, but go to the oldest one with an actual backlog value.
So I have the following raw data in Sheet1
[Code] .....
Now I want to put this raw data in Sheet 2
I use this formula to pull the data =SMALL(Sheet1!C3:C5,1)) but this returns the B4 value to me, which I don't want as A4 is 0.
I've been thinking about somehow using VLOOKUP or IF to sort this out but I am stuck.
View 3 Replies
View Related
Jul 6, 2007
I have been trying to search for either a function or VBA that will perform the following:
If I enter I want all months >=1/1/07 and <=3/31/07 it would tell me that the months in between are January, February and March and so on with all dates.
View 3 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 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Feb 20, 2014
I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.
As an example:
a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00
In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.
Note: the formula needs to work effectively across the boundary between two consecutive years.
View 1 Replies
View Related
Apr 29, 2007
I would like to create a formula that compares a number in one cell to the dates I have displayed in two different cells. If true, then I want it to display the Currency value from another cell.
______A________B________C________D_____
|1| Due Date | Amount | 1/01/2007 | 1/15/2007
|2|___4________$98_______________________
A2 is the DAY it's due
B2 is the AMT that is due
I can create an IF...THEN statement where if A2 is >= C1 BUT less than D1 then the value is B2, BUT it only works if I change A2 to an actual date which means i have to change it every month...which would not help with forecasting future transactions. So in the example above, the value would be in C2 and not D2.
View 9 Replies
View Related
May 9, 2014
I would like to compare % difference between date range through pivot table
Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500
How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?
View 2 Replies
View Related
Dec 15, 2006
In A8 I have a number 1 - 5.
This identifies the weeks in a month 1 - 4 for four week months. The 5 is for a five week accounting month.
In A9 I have a day of the week it could be any day formatted as dddd
In A11 I have the 1st day of the month 01/01/2007 formatted as ddd/yyyy
I need a formulae that will look at the number in A8 i.e. 2
Then look at the day in A9 i.e. Tuesday
Then look at the month in A11 i.e. January
Based on all of that I want it to return the date of the 2nd Tuesday in January 2007 in B11.
In a five week month I need it to return the last Tuesday of the month.
Weeks per month are show in starting in D11 for January.
View 9 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
Mar 20, 2012
I have a start date, and an end date for a promotion. However, I am trying to create some unique keys for a VLOOKUP, so I can combine the data I have by day, with the data I have for promotional start/end dates.
So, simple question, if I have a start date and an end date, is there was way (either using excel or vba) to pull out all the dates within that range and display them on a spreadsheet?
For example, I have 21/01/2012 and 28/01/2012 as the start and end dates. I would want to display 21/01/2012, 22/01/2012, 23/01/2012 etc.
View 3 Replies
View Related
Jan 13, 2007
I am attaching an Excel file where I am trying to calculate lead time (even if a negative number of days) to a next activity based on projected dates based on a 'cycle start date'. I am trying to perform this with e complicated IIF statement which you can see from the attached file.
Note: this file is created from a vb.net application at runtime where this excel file is actually an xlt template. I attached it as an xls to make it easier to look at. EDIT: I have a flag in the Annual/Semi-Annual that blanks out cells that are not needed if Annual. It causes my LeadTime Col. to blow chunks and I can't seem to get past an error in the formula since adding this.
=If(F3>=NOW(),F3-NOW(),If(G3>=NOW(),G3-NOW(),If(H3>=NOW(),H3-NOW(),If(I3>=NOW(),I3-NOW(),If(J3>=NOW(),J3-NOW(),"CycleStart Null"))))). Replaced Zip file
View 2 Replies
View Related
Mar 27, 2008
I've got a 2007 Excel spreadsheet that will count dates within a range using the COUNTIFS function. I am having to send this spreadsheet to users that only have Excel 2003. I need to know how to convert this so it works in 2003.
Since I'm doing this for a fiscal year I have to use a date range. Here are my two formulas I'm trying to convert:
=COUNTIFS(D9:D25,">3/1/08",D9:D25,"<6/30/08")
and
=COUNTIFS(D9:D25,">7/1/08",D9:D25,"<6/30/09")
View 4 Replies
View Related
Dec 22, 2008
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
View 5 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
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Jun 4, 2014
I've used conditional formatting to highlight dates which are due this week and next week, but now I need to highlight which dates are overdue.
Each row is a task/job.
I have a 'Due Date' column.
I also have a 'Date Completed' column.
How do I highlight the overdue dates based on their due date (not today's date)?
And once I have entered a date in the 'Date Completed' column, I need the overdue highlight to be removed.
View 3 Replies
View Related
Oct 17, 2008
I am trying to project the next 12 month-end dates, based on today's date. I can do that using the EOMONTH function ... see exhibit below ... present month, 1 month out, 2 months out, last month. However, this workbook must be sent to many people and many of those folks will not have EOMONTH functionality because that requires the Analysis Toolpak functions to be added in. How can I accomplish this using standard Excel functions?
Present Month >>> =DATE(YEAR(NOW()),MONTH(NOW()),1)
One Month Out >>> =DATE(YEAR(EOMONTH(NOW(),1)),MONTH(EOMONTH(NOW(),1)),1)
Two Months Out >>> =DATE(YEAR(EOMONTH(NOW(),2)),MONTH(EOMONTH(NOW(),2)),1)
Eleven Months Out >>> =DATE(YEAR(EOMONTH(NOW(),11)),MONTH(EOMONTH(NOW(),11)),1)
View 8 Replies
View Related
Nov 8, 2012
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
View 7 Replies
View Related
