How To Translate Number Of Months Into Future Date
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
ADVERTISEMENT
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
Apr 8, 2009
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View 3 Replies
View Related
May 20, 2013
Keep getting compile error.
Sub CreatingCT()
'
' CreatingCT Macro
Dim ConsolidatedTrend As String
'Path to File
ConsolidatedTrend = "(d:2013"&(IIF((MONTH(NOW())+1>9),"(1","(0")&(MONTH(NOW())+1)&") "&TEXT(DATESerial(YEAR(NOW()),(MONTH(NOW())+1),1),"MMM")&"Consolidated Trend - "&YEAR(NOW())&" "&MONTH(NOW())+1&" + "&(12-(MONTH(NOW())+1))).xlsb)"
ActiveWorkbook.SaveAs Filename:=ConsolidatedTrend, FileFormat:=xlExcel12, CreateBackup:=True
End Sub
View 5 Replies
View Related
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
Feb 3, 2014
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
View 9 Replies
View Related
Jan 22, 2009
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 9 Replies
View Related
Sep 30, 2011
Just wondering if there is a formula where I can change the date an employee started working into the number of months employed??
Example, John Smith started here in January 2011. I would like the formula to return 8 months.
View 9 Replies
View Related
Jan 12, 2008
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 9 Replies
View Related
Nov 15, 2008
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 7 Replies
View Related
Dec 13, 2011
I'm trying to write a function that takes a date in format YYYYMM and adds/subtracts a number of months.
for example: newDate(YYYYMM,numMonths)
newDate(201109,5) would give 201202
newDate(201102,-4) would give 201010
I was thinking i could use the left/right function to retrieve the month and year ........
View 7 Replies
View Related
Apr 9, 2009
I have 3 columns of data, column C has a number representing a length of time in months. I need to add a number to the end of each row (Column D), depending on the number in column C. Example:
If column C has a number between 0 and 3, the number to go in D would be 1. If column C has a number between 4 and 6, the number to go in D would be 2. Here are the list of possiblities (only 5):
0-3 = 1
4-6 = 2
7-9 = 3
10-12 = 4
12+ = 5
This will form part of a macro, so a VBA solution would be ideal. I thought of having a named range and looking up each row with a list of all possiblities. But i see that as rather crude. I'm hoping someone has a more logical solution they could share.
View 2 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
Nov 24, 2012
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
View 2 Replies
View Related
Aug 2, 2006
I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date.
If Me.Cells(.Row, "N").Value = "O" And Me.Cells(.Row, "A").Value = "" Then
Me.Cells(.Row, "A").Value = "1"
End If
View 2 Replies
View Related
Dec 6, 2012
I am working as a private consultant for a company, and have put together a series of Excel applications, producing charts and providing some statistical guidelines. I will move on from here soon, and will take my work with me. I want to leave this product with the company, but it will become obsolete without some formula maintenance. Therefore, I would like to be able to have the program basically stop working at a future date. I don't really care if it freezes up, deletes itself or implodes. If not I am also afraid many copies of this program will be made and sent to others in my small community.
View 3 Replies
View Related
Nov 9, 2011
I'm trying to calculate a date in the future and subtract one day.
The date entered in N38 is 11/09/11.
The term entered in months in C8 is 24.
I want N39 to return the date 24 months in the future minus one day.
So N39 should dispay 11/08/13
How the heck do I do that?
View 5 Replies
View Related
Jul 25, 2007
I am trying to create a spreadsheet that will automatically calculate a date in the future.
The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.
If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.
It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd
Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.
View 9 Replies
View Related
Oct 20, 2012
I need to input a future date and time into one cell. For example, if I have a member ship that ends on October 20th at 3:00 pm exactly the kind of format you get when you enter the function "NOW". I would like to put that into one cell and then be able to add and subtract from it.
For example I would like to do something like this, if I had inputed the date and time in A1:
"=Now-A1", it would be cool if I can get the remaining hours or minutes along with how many days are left but not completely needed.
It would also be beneficial if I can drill down further and do it by time zone as well.
View 2 Replies
View Related
Dec 31, 2013
I have some real estate rental so what I did 7 columns and its includes: name, phone number, ....., contract start date, and contract expiry date.
my question is how to make the row color changes depends on the expiry date
for example, the expiry date is 12/10/2014. I want the the row to be red when 12/10/2014 comes.
View 5 Replies
View Related
Mar 14, 2013
I need to create a spreadsheet which will have a start date and duration on programme. Is there any way I am able to automatically insert the End dates based on the weeks on the programme?
Additionally, can a function insert a date for the reviews which should be every two weeks into seperate cells?
Name
start date
weeks on programme
End date
1st review
[Code]...
View 1 Replies
View Related
Aug 25, 2007
I am using the same structure to update another column, under the same sub:
If Target.Column = 5 Then '
Select Case Range("d69").Value
Case "CB1W"
ActiveCell.Value = DateAdd("ww", 1, Now())
Case "CB2W"
ActiveCell.Value = DateAdd("ww", 2, Now())
Case "CB3W"
ActiveCell.Value = DateAdd("d", 21, Now())
Case "CB4W"
ActiveCell.Value = DateAdd("d", 28, Now())
Case "CB5W"
ActiveCell.Value = DateAdd("d", 35, Now())
Case "NI"
ActiveCell.Value = ""
Case Else
ActiveCell.Value = ""
End Select
I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)
I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.
View 9 Replies
View Related
Dec 27, 2007
I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.
I currently have the weekends covered, but am stumped on the holidays.
(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements:
IF today + 28 = Mon.-Fri., then give me today + 28.
IF today + 28 = Sat., then give me today + 30.
IF today + 28=Sun., then give me today + 29.
I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.
View 9 Replies
View Related
Sep 15, 2009
I'm trying to design an excel sheet where a user will input a date of enrollment, and basing on this date, we would like to get a followup date after one month, and again followup after 6 months. However, I would like it to skip the weekends.
I tried it using: =EDATE($C$2,1) works ok, however =EDATE($C$2,6) gives me a date on Sunday.
View 9 Replies
View Related
Aug 4, 2009
I need to calculate the difference in Years, Months and Days between:
Date 1 = TODAY()
Date 2 = 4 years after a date in cell A1, which will always be earlier than today's date
(A bit of backround - I have certain risk management procedures that have a lifespan of 4 years. I want to calculate the time between now and 4 years after the date the procedure was completed, essentially to see how long before they have to be redone).
So far I have:
=DATEDIF(A1+4,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"
But that returns #NUM!.
Removing the +4 obviously just calculated the difference between the date in A1 and today, but I need the date in A1 PLUS 4 years and today.
I have also tried:
=(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1))-TODAY())/365.25
which works in theory, however:
a) no consideration for leap years
b) does not return nY, nM, nD - only the decimal.
However I would be happy to use this method if I could convert it to Years Months Days.
View 11 Replies
View Related
Sep 16, 2009
I'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
View 14 Replies
View Related
May 7, 2014
I had a Calendar Control (2007) but now stuck using 2013. I have to change all of codes related to my Calendar Control to a Month View Control. Problem is I had 4 command buttons beside my calendar to automatically add value to the calendar:
My Code is in a userform. Standard code for MonthView:
[Code] ....
Basically it was “highlighting” the todays date but if one of the cmd_button was clicked then it would highlight the today’s date plus 15 (making the highlighted date, if we take today as an example: 2014/05/22).
Now with the Month View is there a way to select the exact same thing? I mean I know if I deactivate the “Show Today” in the properties but is there a way to make the selected date to have the “Circle in Red” around the date IF I click on a command button?
View 4 Replies
View Related
Sep 11, 2009
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
View 14 Replies
View Related
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 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
