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 RepliesThree 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")
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.
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.
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 RelatedI am using a worksheet change event to display the date and time that certain cells are manually changed. However, I would also like this to happen when a cell displaying a value based on a formula changes.
View 1 Replies View RelatedI 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 want to show a date like 18-12-2008. This wil normal work with =()TODAY-356
But when I output text with this formula it will only show numbers:
="date "&TODAY()-365&""
I want to subtract 1 year of today.
I tried to set my cell to the date format but this did not work.
I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
View 2 Replies View RelatedI'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.
Atm, it reads some like this
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
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].....
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?
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 3 Replies View RelatedI 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.
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?
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.
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..............
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.
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.
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?
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
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 RelatedA1 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 RelatedSomeone 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 RelatedStart 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"
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
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?
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?
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