# 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
Mar 6, 2006

I came up with a formula that i thought should work but apparently not so could someone be kind enough to post a formula that will take the date in one cell and place it in another but showing 6 months later.

View 9 Replies
View Related
Apr 18, 2008

A1 contains an entered date (4/18/08). B1 contains an entered number representing months (6). I want C1 to subtract 6 months from 4/18/08 and then show the rounded down date of 10/1/07.

View 9 Replies
View Related
Apr 17, 2014

Someone will enter their financial year end in the worksheet e.g. 31/03/2014 and I then have 12 cells below it called Month 1, Month 2 etc up to Month 12. I need the cell next to Month 1 to calculate what it would be... so for a Year End of 31/03/2014 month 1 would be April and this needs to apply to the 12 months.

View 2 Replies
View Related
Mar 21, 2013

I'm trying to split an amount within a given month:

Customer

From

To

Total number booked

Total amount booked

A

3 March 2013

5 May 2013

458

Â£5,000

Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?

I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?

Customer

Month

Monthly number booked

Monthly amount booked

A

March 2013

207

Â£2,258.06

A

April 2013

214

Â£2,338.71

A

May 2013

30

Â£322.58

View 2 Replies
View Related
Jul 31, 2013

I have a excel file which deals with accounting systems. It has the Loan date in Cell B6 and Number of dues in cell B7. I need the column from B11 : B34 to be filled with due dates for every month. Suppose if the loan date is 12/05/2013 and number of dues is 10 then in B11 I should get 12/06/2013 , B12 it should be 12/07/2013 and B13 should be 12/08/2013 and so on based on the number of dues.

View 3 Replies
View Related
Jun 5, 2014

I have a date stored in a variable and I need to add month(s) to that date. For example if I have 10/1/2013 in a variable if I add 1 to that month the result should be 11/1/2013. I am using the following formula but the problem is in the result date format, which shows as 1/31/1990, if I add 2 to the original date, the result is 2/29/1900 which ideally should have been 12/1/2013.

My formula is :

ActiveCell.Value = "=date(year(" & last_date & "),month(" & last_date & ")+" & i & ",min(day(" & last_date & "),day(date(year(" & last_date & "),month(" & last_date & ")+" & i & "+1,0))))"

Any other formula to add months to a given date?

View 1 Replies
View Related
Jun 9, 2009

I want to return a "total" result for all the cells which contain a date within the last 12 months from todays date.

View 9 Replies
View Related
Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:

*LU = lesson units; 1 LU is 45 minutes

Holidays:

28-Nov-13

29-Nov-13

08-Dec-13

09-Dec-13

25-Dec-13

31-Dec-13

01-Jan-14

[Code] ...........

View 7 Replies
View Related
Nov 19, 2009

Three columns.

A - Date last checked

B - Due Date

C - Actual Date checked

Currently column B simply has =A+84 and will display a date 3 months in future.. Great..

However now i want column B to still display the 3 month date - unless there is a date in C, whereby i want B to then display OK. SO what im sort of needing is =A+84 (or if somethings in C then "OK")

View 3 Replies
View Related
Dec 9, 2009

I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).

I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.

For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?

It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.

View 9 Replies
View Related