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


Calculate Number Of Months Between Two Given Date

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

Adding # Of Months To Calculate A Date

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

Calculate What Months An Employee Worked By Start And End Date

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

Date Function - Calculate Ratio Between Months Based On Working Days?

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

Roll On Date 11 Months In One Cell When Another Cell (date) Is Amended

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

Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date

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

Calculate Date Difference & Use Current Date If Cell Empty

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..

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

If Date Cell Less Than Todays Date Calculate Difference In Days

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

Creating View By Filtering 2 Columns By Date (both Within 3 Months Of Today Date)

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

Calculate Next Due Date Based On Start Date Frequency And Current Date?

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.


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

Find Starting Date And Fill In The Following Months Until End Date

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.

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.

Sub Macro1()
Dim mainrange As Range
Dim rng As Range


View 3 Replies View Related

Filter Data By Date Within 3 Months Of Today Date?

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

Calculate A Due Date From A Date In Another Cell

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

Elapsed Months (many Months Have Gone By Since Todays Date)

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

Calculate Start Date From Finish Date Minus 14 Working Hours

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

Calculate Number Of Days Between Two Date Within A Current Month Including End Date

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

How To Calculate Average Of Values Against Selected Date Of Each Month Within Date Range

Jan 2, 2014

I have a table that looks like this (its basically a historical data of a stock exchange):

January 4, 2010


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

Calculate Timecycle From Open Date To Close Date?

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

Calculate End Date From Start Date & Number Of Weeks

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

Calculate A Cell With Date

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

Date More Than 11 Months Using VBA

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

Add Months To Date

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

Add X Months To Date

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:


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

Calculate Date Of The Sunday Before A Date

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

How To Get The Date Of 3 Months Ago From Today

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

Sumproduct Between A Date And Next 6 Months

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.


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

Increment Date Every 6 Months?

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

Subtracting Months From A Date

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

Add X Months Or Days To Date

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

Copyrights 2005-15, All rights reserved