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.
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))))"
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.
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?
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
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.
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.
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.
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.
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.
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.
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).
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:
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?
I am using a stacked Data chart with Layout #5. See attached. I calculated in a table below the % difference between the 2 months, but is there a way to display this % difference of the 2 months either in the chart or in a table (or both)? Is there a way to modify the table only? Or is there a different type of chart that can display this?
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
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).
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