Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Calculate Date Six Months From Cell Date


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Calculate Number Of Months Between Two Given Date
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 Replies!   View Related
Adding # Of Months To Calculate A Date
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 Replies!   View Related
Calculate What Months An Employee Worked By Start And End Date
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 Replies!   View Related
Roll On Date 11 Months In One Cell When Another Cell (date) Is Amended
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 Replies!   View Related
Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date
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 Replies!   View Related
Calculate Date Difference & Use Current Date If Cell Empty
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 Replies!   View Related
If Date Cell Less Than Todays Date Calculate Difference In Days
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 Replies!   View Related
Calculate A Due Date From A Date In Another Cell
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 Replies!   View Related
Elapsed Months (many Months Have Gone By Since Todays Date)
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 Replies!   View Related
Calculate Number Of Days Between Two Date Within A Current Month Including End Date
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 Replies!   View Related
Calculate End Date From Start Date & Number Of Weeks
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 Replies!   View Related
Calculate A Cell With Date
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 Replies!   View Related
Formula For 6 Months From A Certain Date
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 Replies!   View Related
Subtract/Take X Months Off Date
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 Replies!   View Related
Add Months To Date
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 Replies!   View Related
Date More Than 11 Months Using VBA
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 Replies!   View Related
Subtracting Months From A Date
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 Replies!   View Related
Add X Months To Date
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 Replies!   View Related
Add X Months Or Days To Date
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 Replies!   View Related
Count Unique Months Between Date Ranges
I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?


View Replies!   View Related
Sum Product :: Within The Last 12 Months From Todays Date
I want to return a "total" result for all the cells which contain a date within the last 12 months from todays date.

View Replies!   View Related
OR Or IF Adding: Display A Date 3 Months In Future?
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 Replies!   View Related
Date Differences In Days, Weeks Or Months
I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...

I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007

or maybe

11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007

...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.

The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...

View Replies!   View Related
Popup Message If A Contract Date Expires In Three Months
Is it possible to have an automatic pop up (warning) message if the (contract) date (in cell U3) expires in three months. In the message should also be mentioned the contents of cell A3 + the contents of cell K3 + the contents of cell U3. I don't want to use conditional format but need to show a warning.


View Replies!   View Related
Get Month & Year X Months From Current Date
I am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting.


strDate = Format(DateAdd("m", 1, Date), "mmm-yy")

strDate = Format(DateAdd("m", 2, Date), "mmm-yy")

However if I use this, I get the current months date correctly (like May - 08)


strDate = Format(DateAdd("m", 0, Date), "mmm-yy")

View Replies!   View Related
Calulate The Number Of Years And Months From A Specific Date
I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

View Replies!   View Related
Number Of Months In Year 2007 From Date Range
I have a "start date" and an "end date". Is there any way to tell how many months are included in 2007 from those two dates using a formula? For instance, from 01-Feb-04 to 01-Feb-07, there is 1 month in 2007 (January). And likewise, 01-Jan-05 to 01-Jan-08 there is 12 months in 2007.

As noted, I have the start and end dates. I don't have the "how many months in 2007".

Start Date End Date How many months in 2007
01-Feb-0401-Feb-07 1
01-Jan-0301-Jan-06 0
01-Apr-0401-Apr-07 3
01-Feb-0301-Feb-06 0
01-Mar-0401-Mar-07 2
01-Feb-0401-Feb-07 1
01-Dec-0401-Dec-07 11
01-Jan-05 01-Jan-08 12

View Replies!   View Related
Populate List Of Months Based On Date & #
I have a # of months in P11, and a Start Date in P12:

P119 Months126/1/2024

I need to excel to autopopulate monthly dates from the Start Date = to the # of months displayed in P11: ...

View Replies!   View Related
Adding Years, Days, Months To Date/time Format
I have some cells which must be in the format 15/06/2007 15:25

I then need to add either days, months or years onto it.

Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00


View Replies!   View Related
OFFSET With Today's Date To Show Last 6 Months Data
I have a data table with monthly data in columns (65 rows deep), with the months (in format dd/mm/yyyy but showing as Dec 05) running across Row 4.

I want to be able to use OFFSET to identify the current and previous 5 months, in order to dynamically chart various items in the last 6 months worth of data.

The charting bit I'm okay with, and I realise I need to assign Names for this to work, but I'm struggling with the OFFSET & date combination.

I have the following but it starts from a defined reference cell;

=OFFSET('BO Data'!$L$4,0,('BO Data'!$4:$4)-1,1,-6)

View Replies!   View Related
Return Months Between Start Date & End Dates
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 Replies!   View Related
Date Formula; Gets To 2 Years & 11 Months The Cell Will Go "YELLOW" For 1 Month Only Before It Turns "RED"
I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".

View Replies!   View Related
IF Date Formula: IF Formula, That States If A Date Is More Than A Month After The Date In The Cell Then It Is Timely
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View Replies!   View Related
Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...


View Replies!   View Related
Date Formula: Date Another Cell Rounds Up To Jan 1st Of The Next Year
what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.

View Replies!   View Related
Check Cell Has Date & Insert Current Static Date
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos

View Replies!   View Related
Sort Table In Date Order But With The Date Nearest To Today's Date At The Top
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...

The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...

I use xl 2003.

View Replies!   View Related
VBA Comparing Date In Cell To Today's Date
I need to VBA code that will loop through the active sheet for all rows, column C2 through however many rows there are. Col C contains a date in the format: mm/dd/yyyy. I need the VBA to compare this date to today's date+30 days and if the date in C2, etc. is greater than today's date + 30 days, shade the row a particular background color (e.g., 3).


View Replies!   View Related
Search & Replace Date In Date And Time Cell
I have several hundred rows of date/times which I need to modify just the date (for example 08/23/2007 11:00 to 08/15/2008 11:00). Each row may have a different time so it is just the date I'm targeting for change.

If I manually use the replace dialog, replacing 08/23/2007 with 08/15/2008 works just fine. However, if in code I attempt to use the Cells.Replace function, it does not locate any data to change.

I have found that if I search for the string 8/23/2007, the dates are located and changed. Only when I attempt to find the fully formatted date 08/23/2007 does the function fail.

View Replies!   View Related
Prevent Automatic Date Format When Referencing Date Cell
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.

View Replies!   View Related
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View Replies!   View Related
Check Date Cell Is Today's Date
How to check cell from a " DATE" column has todays date or not?

View Replies!   View Related
Date And CONCATENATE (add The Date As A Referenced Cell)
I'm having a problem getting the date to show up when I add the date as a referenced cell to a formula using CONCATENATE. The date shows up as a serial value instead of a date format. The formula that I'm trying to get to work is in cell J7 on the attached file.

View Replies!   View Related
Highlight A Cell If The Date In It Is Before Today's Date
how I can create a formula that would highlight the cell in a colour if the date was past todays date? This is what I'm doing - I have the expiry date of people's insurances. so e.g todays date is 8 th January, so if the insurance had expired 31.12.08, i would want the cell to be highlighted red. I think it may be an IF function but I cant remember.

View Replies!   View Related
Delete Rows If Cell Contains Date Outside Date Range
I've found similar queries on the site, but none seem to handle multiple dates, and my attempts so far have been futile!

Requirement:
I press a button on Sheet1 and am prompted to enter a start date and an end date.

Depending on the dates I enter, all rows on Sheet2 which fall outside the Start/End date range will be deleted.

All dates to be checked against on Sheet2 are in a single column, and all rows are populated, there are no blanks. Dates are in the format dd/mm/yy

View Replies!   View Related
Date Range: Reference A Single Date And Output Date Ranges
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 Replies!   View Related
How To Calculate Date
how to calculate the date for one year. If i have 24-Jun-08 (A1) i need to calculate 24-Jun-09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.

View Replies!   View Related
Calculate The Date..
Is there a formula for calculating date for every 2nd thursday of the month excluding holidays?

View Replies!   View Related
IF To Calculate If One Date Is Greater Than Another
I am having great difficulties getting the following formula to calulate wether the date in cell f2 is greather than of less than the given date.

=IF(F2<="18.11.06", "No Cover", "Under Warranty")

f2 = 14.09.02

This does produce the wanted result of "No cover" however if the date is 14.09.07 and therefore greater the 18.11.06 i will not get the expected result of "Under Warranty"

View Replies!   View Related
Calculate Redemption Date
attached spreadsheet, columns A-E are given data, col B shows the redemption frequency, col C shows the advanced notice period and col E shows the our first investment date. I would like to display the result in one column showing the next redemption date..

e.g. for Fund 1, it's redemption term is quarterly 30 days notice, which means.. next redemption period will be 30/09/2007 (get money back) and we have to give the redmeption notice latest on 31 Aug 2007 becos of 30 days advance notice to exit the fund

e.g. for Fund 2, it's monthly 90 days notice, which means if we put in a redemption notice today then 90 days from now on will be 8 november, hence can get money back on 30 November 07.

What formula shall I use to display
- next available redemption date
- exit date (get money back)

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved