Calculate Date Six Months From Cell Date
Jan 25, 2010
I have a start date for a contract in cell AM1. I need AN1 show a date six months from the start date in AM1. I am fine with that part.
However, I would like for AN1 to calculate not only a six-month date but also to auto-update to the next six month date from AM1 once the first six month date is about, let's say, one month past. What I am doing is calculating when a six-month inspection needs to be completed; these inspections are ongoing, so I need them to auto-update.
So let's say a contract was awarded today, 25 JAN 2010. That would be value in AM1.
I want AN1 to produce a date six months from 25 JAN 2010, which would be 25 JUL 2010 (yes, this way of calculating the six month date is fine). Then around, let's say, 25 AUG 2010, I want AN1 to auto-update to produce the next six month date, which would be 25 JAN 2011 (six months from 25 JUL). Obviously the function would need to relate to TODAY() in some way.
View 11 Replies
ADVERTISEMENT
Apr 12, 2006
Start date: 12/04/2004
End date: 12/04/2006
The formula should give the answer to 24 months
Example 2
Start date: 12/04/2004
End date: 13/04/2006
The formula should give the answer to 25 months
When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not
show 25 months for "Example 2" as it is still within the same month "April"
View 14 Replies
View Related
Jul 30, 2007
Is there a way to add # of months to a date and then find out what would be the exact date?
For example if cell A1 = 04/30/07 and Cell B1 = 14 what is the statement that would give me 06/30/08 in Cell C1?
Also, how can I modify the statement if Cell B1 = 14.5 to get 07/15/08?
View 9 Replies
View Related
May 15, 2009
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter.
It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
View 2 Replies
View Related
Jul 25, 2014
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
View 2 Replies
View Related
Jun 5, 2009
I have had a look via search for similar but I'm not good enough to recognise whether other threads bear any relation to my query.
I have a small spreadsheet with basic conditional formatting (my level!) where we record agency staff for eleven months service to enable us to ask them to take a four week break.
I want to add a formula or similar to automatically roll the date forward eleven months when we amend the re-commencement date on their return.
E.g. we type in A1 01/01/09, B1 updates to 30/11/09
Is this possible?
View 10 Replies
View Related
May 19, 2009
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
View 2 Replies
View Related
Sep 22, 2007
i am trying to create a forumla in a column (D in my expamle below) to count the number of days between two dates. Knowing that col D will change everytime the @now date changes - but thats ok..
COL A COL B COL D
ROW 1 Open Date Closed Date Count of days open
ROW 2 4/1/07 BLANK CELL Formula result here
ROW 3 4/1/07 9/5/07 Formula result here
if col b has a date then subrtract col a from b and display the # result, if col b is blank then subtract col a from location I store the @ now date - say Col ZZ Row 99? so no matter there is a count of number of days in every row in column D
View 3 Replies
View Related
May 13, 2008
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
View 7 Replies
View Related
Mar 20, 2014
Trying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
View 5 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 4, 2013
I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).
What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.
I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.
Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.
VB:
Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]
I have tried to make this dynamic but have been unsuccessful so far.
Spreadsheet with data&code is attached.
VB:
Sub Macro1()
Dim mainrange As Range
Dim rng As Range
[Code].....
View 3 Replies
View Related
Mar 20, 2014
I'm trying to filter 2 date columns to include only data containing dates within 3 months of today's date.
I see there is a data filter option for "next quarter" but not 100% sure if this covers the quarter from today's date?
View 3 Replies
View Related
Jan 18, 2007
What is the formula to calculate a due date from a date in another cell?
If the date is 06-12-06 and the due date is the end of the month six months later, 12-31-06, how do I do this?
View 10 Replies
View Related
Jul 19, 2009
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies
View Related
May 7, 2014
I needs a formula (not VBA) to calculate the required start date.
I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00
My working week is from monday u/i friday and every day I work from 08:00-16:00
At what time do I have to start the job to get it done in time.
The formula should give this result: 01-may-2014 15:00
View 3 Replies
View Related
Apr 2, 2009
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
View 9 Replies
View Related
Jan 2, 2014
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
View 1 Replies
View Related
Jun 12, 2014
Looking to calculate timecycle from open date to close date. Having trouble trying to figure out how to have the formula look at the close date cell and if it is blank go to the next cell in the row to find a close date and subtract the open date from the close date to get the total number of days in the timecycle. See attached example.
View 1 Replies
View Related
Jun 26, 2007
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
View 5 Replies
View Related
Apr 2, 2009
I would like a macro to calculate the number of days for a date if cell has date. For example If cell A has a date cell B should automatically do the calculation for =TODAY()-A1
Cell ACell B
3/21/09Calculation from Macro
3/22/09Calculation from Macro
3/26/09Calculation from Macro
View 9 Replies
View Related
May 5, 2008
My boss asked me to create a workbook that notify you when the date in the sheet is more than 11 months old.
In column A has list of street names and in column B is where the dates of completed Footpath Replacement occured by the contractors.
We have to check those new footpaths before 12 months up otherwise contractors will not have to return to fix any fault they made.
So what is the code be in Visual Basic Editor with the message box to warn you of any date that is 11 months old?
View 9 Replies
View Related
Jul 10, 2008
I have managed to create a formula to add a date (contained in cell E3) to a number of months (contained in cell F3).
This formula is in cell G3
=DATE(YEAR(E3), MONTH(E3)+(F3+1), 0)
This works fine. Apart from before I put information into E3 and or F3 then G3 displays "31/01/1900". I wish G3 to remain empty until information is entered into both E3 and F3.
I have used data validation on cell E3 to restrict users to enter a date between 01/01/1980 and 01/01/2099.
View 9 Replies
View Related
Jan 30, 2008
I have a macro that adds a text in column H if criteria is met. In column Q I have a date field. I'd like to add 3 months to that date and put the result after my text below, so it would look something like this:
"FORCE-MATCH CANDIDATE < $5K ICDP, 01-04-2008".
Is it possible to do relatively easy?
Sub ForceMatch()
Dim ws As Worksheet
Dim line As Integer
Dim Endline As Long
For Each ws In Worksheets
If ws. Name <> " Total" And ws.Name <> "Batch" And ws.Name <> "Summary" And ws.Name <> "PivotTable" And ws.Name <> "Fields" Then..............
View 3 Replies
View Related
May 13, 2014
I have a date and need to formula to tell me the date of the Sunday that occurs before it.
For example I want to it tell me that the Sunday before 13/05/2014 is 11/05/2014.
View 5 Replies
View Related
Aug 30, 2013
I have to figure out the date of 3 months ago from today. The rule is:
1) if today is the month end (e.g. 30/06/2013), then 3 months ago will also be the month end (e.g. 31/03/2013).
2) if today is not the month end (e.g. 10/06/2013), then 3 months ago will have the same day (e.g. 10/03/2013). But this has some exceptional case, because some month has 30th while other month does not. For example, if today is 30/05/2013, then 3 months ago will be in February which does not have 30th. In this case, just make it as the month end of 3 months ago.
View 5 Replies
View Related
Jan 24, 2013
I have written the formula below that returns to me a total value of all products named ABCD where date is greater than in A3 which works fine.
=SUMPRODUCT(($CE$4:$EW$4>$A$3)*($A$5:$A$85="ABCD")*($CE$5:$EW$85))
CE4:EW4 = date range (Format MMM-YY)
A3 = A single date (format MMM-YY)
A5:A85 = Column of product names
CE5:EW85 = values
I am looking to add to this criteria to return where date is > A3, but only for the next 6 months.
View 9 Replies
View Related
Dec 22, 2013
If I have a date in a cell. Let's say A2 is 12/1/2012. Six months later I would like that date to increment 6 months and display 6/1/13. When the date in A2 is 12 months old I want the date to increment another six months and show 12/1/2013. Basically, every time the date reaches a 6 month anniversary from the initial date found in A2 it would increment another 183 days.
The reason I'm trying to do this is to create a rolling reminder so I can followup with customers every 6 months.
If it's not possible without creating some sort of final end-date to the cycle I guess it would be okay if it ended after 3 or 4 years.
What would be the best method to do this?
View 2 Replies
View Related
Aug 14, 2007
If I have a date in Cell A1, what formula do I need to put in cell B1 to subtract 6 months.
eg If A1 = 14/08/2007 I want B1 to read 14/02/2007 and not 12/02/2007 which it reads when subtracting half a ywears worth of days
View 9 Replies
View Related
Jul 29, 2009
I use [=IF(F15="","",EDATE(F15,-D15))] to track time frames. The problem is Edate works great for months but on occasion I need days, like 40 days. In cell D15 I have a list where the user must choose from, but one of the choices needs to be in days so the Edate won’t work anymore.
View 5 Replies
View Related
