Date Splitting Between Two Dates
Feb 26, 2008
I think I need macro to do this but not sure.
I have a variable start date in cell A1 and end date in cell B1.
Lets say, start = 04/01/07 in A1 / end = 3/31/10 in B1 (date format used = mm/dd/yy)
I am looking for a way to partition these out in subsequent columns as follows:
A2 and B2 would = 04/01/07 and 12/31/07 (this section ends at the end of the first year specified in cells A1 and B1).
A3 and B3 = 01/01/08 and 12/31/08 respectively
A4 and B4 = 01/01/09 and 12/31/09
A5 and B5 = 1/1/10 and 3/31/10
I would like to be able to enter any start/end date values into cells A1/B1 and have the cells directly underneath split these dates out chronologically as described above.
The number of populated cells in rows underneath would need to grow dynamically based on dates input in cells A1/B1.
I have been chatting up mr.excel.com to many coworkers recently. Excel is becoming a big deal at my office. I hope to see them chekc this site out.
View 9 Replies
ADVERTISEMENT
Jun 12, 2006
I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year.
For Example
If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box.
After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.
View 9 Replies
View Related
Jan 28, 2014
VBA code to split the the date values into various cells. For example 8-Feb-14 in cell A2 will be splitted into three cells (C2, D2 and E2) to have the values:
Cell C2: 8
Cell D2: Feb
Cell E2: 2014 (Note that 20 has been added to the 14 to make it 2014)
I have attached a spreadsheet to understand what I mean. The results are in red and the raw data is in black.
test12.xlsx
View 9 Replies
View Related
May 3, 2009
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
View 5 Replies
View Related
May 14, 2008
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
View 4 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
May 30, 2013
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies
View Related
Feb 13, 2009
In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?
=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.
View 5 Replies
View Related
Apr 7, 2009
i am trying to see if a date is between 2 dates. i have a sql dump and want to create additional colums before creating a pivot. each line was created with a date and i want to create 2 new colums financail year, month. not sure if it is possible with vlookup and that is about as advanced as i get. attached is a summary table.
View 5 Replies
View Related
Aug 21, 2009
I have defined 2 dates ( Starting and Ending ).
Sub Define_Dates()
begdate = InputBox("Please enter the Beginning Date in MM/DD/YY Format")
enddate = InputBox("Please enter the Ending Date in MM/DD/YY Format")
End Sub
I need to delete any row (from LR to FR Step -1) that Col M ( Date ) is outside the Beginning and Ending Date.
View 9 Replies
View Related
Aug 17, 2006
I have this script it does exactly as i want it to do, filter a table of date using two selected date in D3 and D4. There is only one problem: when i choose the dates and run the script it changes them around it around.
e.g
cell D3 = 01/11/2006
cell D4 = 01/12/2006
so it should just show all of november (11)
but D3 will filter using 11/01/2006
and D4 will use 12/01/2006
Sub DateFilter()
Range("a8:r323").AutoFilter Field:=1, Criteria1:=">=" & Range("D3").Value, Operator:=xlAnd, _
Criteria2:="<=" & Range("D4").Value
End Sub
View 7 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
Apr 1, 2008
I have a formula that tries to determine if a date is between two dates. The formula I have is =IF(AND(A1>=A2,A1<=A3),"yes","no")
a1 = 7/31/2009
a2 = 8/1/2008
a3 = 3/31/2010
If the date is between those two dates then "yes", if not then "no". I tried this formula a couple times and it works when a3 is 3/31/2010 but when I try 3/31/2009 for example it does not work. So it seems like there is something wrong with the second date I am referencing.
View 3 Replies
View Related
Jun 25, 2014
formula to input to excel to get the dates rolling down in A cell i want it to go A1(1/1/2014) A2(1/1/2014) then a3(2/1/2014) a4(2/1/2014) a5(3/1/2014) a6(3/1/2014) i have a lot of these dates to put into excel
View 5 Replies
View Related
Aug 15, 2014
I have a date that has to be amended every now and then because the date is changing (bcoz of Night Shift), hence, I need to know a formula that.
IF Time is within 8:00 PM (Aug 15) until 5:00 AM (Aug 16), the Aug 15 will prevail.
View 3 Replies
View Related
Mar 12, 2014
I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.
Example
048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013
I want this to read
048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013
View 1 Replies
View Related
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
Sep 24, 2009
I have a spreadsheet that lists items in column A with a start date in column B. What I need to do is in in column C list the next review date in increments of 5 days, but I only want the next day to show from the current date, not the inputted start date.
For example:
Column A Column B Column C
Item Start Date Next Review Date
Item A 12-Sept-09 27-Sep-09
Can I get a formula to calculate the increments and only show the next calendar date for the review, and then to change to the next date once the previous date has passed.
Alternatively, I have another sheet in my workbook that calculates the dates, is there some way I can display the next future date in a line of dates?
View 5 Replies
View Related
Apr 4, 2013
I have a cell pulling the date from another page and the dates comes across as "2/1/2013- 4/15/2013"in cell B2 and I have the code in sheet 1[code]=IF(OR(Safeway!A43=""),"",Safeway!A43)[code] pulling data from the sheet 2 already in the middle with a code for later use to hide a certain character. I have a condition code set so if the date is past current date, it will turn red but with the 2 dates in the same cell, I am unable to get it to turn red. How do I go about only having the 2nd date show up so the condition works while already having this one code in the cell? I attempted to add the early stages of this program for review.
View 5 Replies
View Related
Jul 8, 2013
I have a problem regarding month formatting
I have 12 columns, Jan 2013 - Dec 2013.
I need to set a format in which if I enter a num eg 15 on the Jan Column, format comes as "15-Jan" format
or If i type 20 on the march column , it comes as "20-Mar" format
I would need it in Date format, because I would be comparing dates later on.
View 2 Replies
View Related
Sep 29, 2013
How to change the date automatically depending on the instructions. If the date of the medical certificate of the Government (MCG) included, automatically private medical certificate (MCP) date will change to the rear on the day before the Government's medical certificate given if the date overlaps
View 3 Replies
View Related
Jul 16, 2013
i have messed up column A which has only dates and i have different date formats like 03/07/2013 and 07/03/2013 i need to have a fixed date for all in B like 03 July 2013
View 2 Replies
View Related
Aug 5, 2009
I am currently having a very painful problem with dates.
Currently I assign a value which happens to be a date to a string.
View 11 Replies
View Related
Mar 9, 2009
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
View 2 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
Feb 3, 2007
dim MyRange as range
dim Col as integer
Set MyRange = ActiveSheet.Range(2,1), Cells(102, 1))
if application.worksheetfunction.Match(ActiveSheet.Cells(5, col),MyRange,0) > 1 then
' it was in the list.....
else
'it is not....
end if
I imagine this isn't the best way and I since I couldn't get past the Set statement. What am I missing? Is the range of dates causing the problem? If my range is one cell in the range it sets the range?
View 9 Replies
View Related
Jun 15, 2007
I want to display 2 dates in one cell and have them custom formatted.
Eg. Display in one cell: 26 Jun - 27 Jun
With text typed: 26-6 - 27-6
(Or something along the lines of)
I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.
View 9 Replies
View Related
Jan 17, 2008
i have a due date column and an adjusted due date column. a proof is allowed to be held 3 days, if it is held longer, the due date is adjusted. for every day over the allowed days that a proof is held, we are allowed 2 more days of production. i want to create a formula that will calculate how many EXTRA days the proof was held, multiply that by 2 and then add that many days to give us the adjusted due date. is there a way to do that?
View 10 Replies
View Related
Aug 22, 2008
how to find the 2nd, 3rd and 4th date from list of dates.....
View 9 Replies
View Related
Dec 10, 2008
I have a column of dates listed at the bottom of this post. I am looking to have a formula in a cell that gives me the earliest date from the column. I have tried Min() and Small() with no success. Is it perhaps the "AM" and "PM" that is throwing things off? A small example of the data:
11/21/2008 AM11/09/2008 AM11/09/2008 PM11/09/2008 PM11/10/2008 PM11/11/2008 AM11/11/2008 AM
and it should return 11/09/2008. Thanks in advance any and all.
Ed
View 9 Replies
View Related
