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
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
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"
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?
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%.
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?
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
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"
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
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.
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?
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
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?
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..............
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.
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?
OR Or IF Adding: Display A Date 3 Months In Future?
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")
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:
...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...
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")
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-Jan-05 01-Jan-08 12
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
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)
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...
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...
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
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).
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.
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.
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.
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!
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
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:
October 19 – October 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.
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.
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"
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)