# Function That Takes Date In Format YYYYMM And Add / Subtract Number Of Months

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
ADVERTISEMENT
Apr 18, 2008

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 9 Replies
View Related
Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is G22e?

EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

EDIT #2: I guess I should probably note that the date in H22 is the result of another formula

=EDATE(G22,12)

View 7 Replies
View Related
Feb 26, 2014

I am looking for a formula which substract a number of workdays defined in a cell from a date defined i nanother cell. For example I have a date in cell B2: 10.08.2014 - this is the due date for service delivery. In cell C2 there is a number of workdays: 84 - this is the duration for the delivery process. I would like to have a formula calculating in cell D2 the date when the delivery process has to start. The tricky point is that B2 is basicly a range of merged cells B2:B6. As an example I am attaching an exmple sheet.

View 10 Replies
View Related
Nov 14, 2006

Need to find out exact date. if I insert 32.5 (32 years 5 months ) in particular cell. my output shld be 10 june 1974 or 10/06/1974. I want to find back date...

View 4 Replies
View Related
May 2, 2014

I have a cells with a date and time in each cell. I want to subtract the number of days between the cells only. Is there a way to do that with the time in the cell? If not, how do I remove the time in each cell?

View 5 Replies
View Related
Apr 26, 2007

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 9 Replies
View Related
Jun 22, 2012

Is there a simple function for calculating the number of months between 2 dates.

Eg 1/5/2012 - 8/9/2020

View 1 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
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
View Related
Jul 25, 2014

I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.

View 2 Replies
View Related
Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

View 6 Replies
View Related
Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is H22.

I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,

"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

I should also probably note that the date in H22 is the result of another function.

=EDATE(G22,12)

View 5 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
Dec 8, 2013

I have subtracted two dates from B2 & B1 using DATEDIF() Function and the results are available in (B5) Year, (C5) Month and (D5) Days respectively. Now the problem is how to Subtract, Add days & Months using borrow, Carry forward to the previous cells (I need formula, function for the above). I have to take 30 days from month ie in C5 if the days (D5

View 1 Replies
View Related
Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

View 5 Replies
View Related
Aug 1, 2013

I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.

This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.

View 3 Replies
View Related
May 15, 2008

I want to obtain from some elements the number of cells it takes to appear:

We have for example A,B,C, and D,

and they appear in the next order:

1A

2C

3D

4A

5D

6B

7C

8A

9A

What I want to know is how much last in appear each element.

1A1

2C2

3D3

4A3

5D2

6B6

7C5

8A4

9A1

For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.

View 10 Replies
View Related
Jul 31, 2009

I have a cell, lets call it A1, w/ an IF statement referencing another cell, which is a function of two values taken from RTD. A1 is either blank or reads "One." I want another cell to tally the # of times that A1 takes on the value "One."

The issue:

I've tried approaching this by using Worksheet_calculate and an IF/Then statement to add 1 each time Range("A1") = "One" however this doesn't work because it keeps adding 1 while A1 reads "One." The calculations seem to fire off every milisecond, so if "One" flashes for 0.5 seconds, I get 500, and if it flashes for 3 seconds, I get 3000. For both cases, I'd like it to just add 1.

View 9 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
Oct 6, 2009

I have a very long if function that takes a value froma cell and then totals the row up to that point.... Is it possible to make this shorter? I have tried to put a sum function in there but that also comes with the nested functions have been exceeded error message...

=IF($S$8=12,P11+O11+N11+M11+L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=11,O11+N11+M11+L11+K11+J11+I11+H 11+G11+F11+E11,IF($S$8=10,N11+M11+L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=9,M11+L11+K11+J11+I11+H11+ G11+F11+E11,IF($S$8=8,L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=7,K11+J11+I11+H11+G11+F11+E11,IF($S$8= 6,J11+I11+H11+G11+F11+E11,IF($S$8=5,I11+H11+G11+F11+E1,))))))))

This one stops short, as it should continue down to $s$8=1.

View 2 Replies
View Related
Jan 17, 2010

I have two questions regarding date format and hope you can provide input.

1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?

2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?

View 2 Replies
View Related
Feb 5, 2010

I need a formulat that will ensure that a cell has 12 numeric characters,a dash, and then 3 more numeric characters. I thought this was a simple formula but I cannot get anything to work.

############-###

View 9 Replies
View Related
Apr 28, 2006

I am trying to format this function to just return the year, but I can't seem to get it to work. It just returns the full date and all I need is the year.

DateAdd("yyyy", -1, Date)

View 2 Replies
View Related
Feb 23, 2012

I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.

I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?

Code:

=VLOOKUP(A2,'C:&E1&[Myfile.xls]Sheet2'!$A:$B,2,0)

View 4 Replies
View Related
May 10, 2007

I have two worksheets which talk together and one of them gives a time stamp using some VBA code

=format(Now(),"dd/mm/yyyy AM/PM"

in the other spreadsheet I have the Now() function which has been working fine. However, when another user went into the spreadsheet went in it, my calculations between the two dates now error. I am presuming its because of the now function not having some formatting defined so is there anyway of changing this value? The cells-formatting will not obviously work as excel is getting confused between the two time types even though the format looks fine. To help the calculation that is messing up is

=IF($L7="","N",IF(DATEDIF($L7,$J$1,"d")>10,"N","Y"))

whereby L7 is the imported timestamp and J1 is now(). The calculation is to show which accounts become inactive after 10 days.

After reading up on the internet I know that to get round the problem I have to make sure that everyone is using the same regional settings. But I just have a couple of questions:-

1. I can understand the date calculation not working on the other persons machine, but when I went back to look at it I was getting the same error. Even though the file is shared, how come my settings didnt 'overwrite' and how come i was affected?

2. Is there a way to make the code work regardless of who opens it in whatever region? Im not sure if there is a fundamental problem but I cant rely on everyone having the same settings because I know that this will cause problems in the longterm.

3. Using the formuale Now(), is there no way of forcing that to a uk date format to stop the errors?

View 9 Replies
View Related
Dec 5, 2012

There is a spreadsheet I use at work where we are told to use an apostrophe before the date to prevent the function bar from converting the dd/mm/yy to dd/mm/yyyy. Why does the apostrophe do this?

View 4 Replies
View Related
Feb 16, 2010

I used the following guide to construct a hyperlink formula which takes me to the coresponding part number entered into a box,

http://www.ozgrid.com/Excel/hyperlink-lookup.htm

It works well in the database I set up with all the part number the problem comes when I tried to use the same hyperlink formula in a diffrent work book referencing the database.

I get a correct returned line reffrence from the macro but it will not open a link to the work book

=HYPERLINK('Database Final.xls'!wbsheet & ADDRESS(MATCH(N16,'[Database Final.xls]DATABASE'!$A$1:$A$9999,0),1),N16 & "s' Info")

View 9 Replies
View Related