Formula for date field

1.I have simple dates in one column (say column A) .
2.In the next column(Column B)  I would like the date five months after 
Column A to be displayed.Eg if Column A has an entry of 9th June 
2007,Column B should display 8th November,2007.
3.A simple formula does not do the job as this does not take into 
account the different number of days in different months!

regards

S.Sanatani
0
sanatani (2)
6/9/2007 2:43:15 AM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
389 Views

Similar Articles

[PageSpeed] 6

Your post is a bit ambiguous since you don't really say how the 
different number of days in months should be handled.

One way:

A1:     <date>
B1:     =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1)



In article <#2gTw$jqHHA.3492@TK2MSFTNGP02.phx.gbl>,
 Sunayan Sanatani <sanatani@mtnl.net.in> wrote:

> 1.I have simple dates in one column (say column A) .
> 2.In the next column(Column B)  I would like the date five months after 
> Column A to be displayed.Eg if Column A has an entry of 9th June 
> 2007,Column B should display 8th November,2007.
> 3.A simple formula does not do the job as this does not take into 
> account the different number of days in different months!
> 
> regards
> 
> S.Sanatani
0
jemcgimpsey (6723)
6/9/2007 2:47:39 AM
The formula below is required Analysis TookPak add-in

=EDATE(A1,5)-1


"Sunayan Sanatani" wrote:

> 1.I have simple dates in one column (say column A) .
> 2.In the next column(Column B)  I would like the date five months after 
> Column A to be displayed.Eg if Column A has an entry of 9th June 
> 2007,Column B should display 8th November,2007.
> 3.A simple formula does not do the job as this does not take into 
> account the different number of days in different months!
> 
> regards
> 
> S.Sanatani
> 
0
6/9/2007 4:09:02 AM
Sat, 09 Jun 2007 08:13:15 +0530 from Sunayan Sanatani 
<sanatani@mtnl.net.in>:
> 1.I have simple dates in one column (say column A) .
> 2.In the next column(Column B)  I would like the date five months after 
> Column A to be displayed.Eg if Column A has an entry of 9th June 
> 2007,Column B should display 8th November,2007.

Is that a typo? Five months after June 9 is November 9, not November 
8.

> 3.A simple formula does not do the job as this does not take into 
> account the different number of days in different months!

When you say "five months", then, what do you actually mean? 150 
days? 5/12 of 365 days? Other?

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
0
6/9/2007 5:01:36 PM
Stan Brown wrote:

> 
> Is that a typo? Five months after June 9 is November 9, not November 
> 8.

That this is not a typo is the main problem!
The actual job undertaken by me is done this way-A certificate is issued 
on completion of a survey and this is valid for a period of five months 
and these five months are counted as above (eg issued on June 9th 2007 
and valid upto November 8th 2007 , 17th January to 16th June 2007 etc.)I 
want to enter only the issue date in A1 and want the excel sheet to 
calculate the validity date of the certificate in A2 using a formula.
Let me try out some of the formulas that have been suggested in the 
other replies.

regards

S.Sanatani

0
sanatani (2)
6/9/2007 5:29:02 PM
In article <MPG.20d4a6ff87af3e0198ad4a@news.individual.net>,
 Stan Brown <the_stan_brown@fastmail.fm> wrote:

> Is that a typo? Five months after June 9 is November 9, not November 
> 8.

Hmmm... then what's five months after January 31? or twelve months after 
February 29th?

Months are, as you've noted, slippery concepts. It's often folly to make 
definitive statements without explicit specifications...
0
jemcgimpsey (6723)
6/9/2007 5:42:58 PM
Sat, 09 Jun 2007 22:59:02 +0530 from Sunayan Sanatani 
<sanatani@mtnl.net.in>:
> A certificate is issued 
> on completion of a survey and this is valid for a period of five months 
> and these five months are counted as above (eg issued on June 9th 2007 
> and valid upto November 8th 2007 , 17th January to 16th June 2007 etc.)I 
> want to enter only the issue date in A1 and want the excel sheet to 
> calculate the validity date of the certificate in A2 using a formula.

Okay, so you actually mean five months less a day.

Make sure you experiment with issue dates like September 30, 2004(*) 
and May 31 of any year.

(*)September 30, 2007 won't reveal a problem.

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
0
6/9/2007 11:49:05 PM
Reply:

Similar Artilces:

add a date without holidays
Hi friends, I have a problem, in a macro I need to calculate a date adding working days only. This add not include saturday / sundays and holidays. For example: 21/5 + 6 days = 31/5 How i can to do ? Thanks in advance and sorry by my english, Christian For adding days, use the DateAdd function. It won't return an invalid date like 31/5. The Weekday function returns the number of the day, for instance this Weekday("22.05.2010" ,vbUseSystemDayOfWeek) returns 6 for Saturday, tomorrow it will return 7 for Sunday. For holidays I can't help...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

How do I customize fields in a "desin form"
I am trying to design my own contact form but am unsure how to do it. I want to remove some ifields that are there by default, and add some fields of my own. For example, I am trying to create a form for family members,, so I'd like to remove business phone # and replace with cell #, and remove company name, and replace with spouse. Any helf is greatly appreciated. -- shellnbreeze Then you would want to post your question in the programming group. -- Russ Valentine "shellnbreeze" <shellnbreeze@discussions.microsoft.com> wrote in message news:1B7ED1F0-CD...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

Edit formula while entering Excel 2000
Is there a way, such as a key combination of enabling the cursor to navigate a formula or line of data while composing it, before its actually entered, and without going to the formula bar to do it? Yeah....I know its niggly, but its niggled me for a long time :-) hi basicly 2 ways to do it. in the formula bar or directly in cell. you seems to know about the formual bar so to edit directly in cell.... on the menu bar>tools>options>edit tab>check edit directly in cell. regards FSt1 "Kevryl" wrote: > Is there a way, such as a key combination of enabl...

Field promotion in a Report.
Hi NG. I have a Table - Quiery an a Report. The Reports contain fields: First_Lastname Adr1 Adr2 ZipCity If this was mailmerge in Word and Adr2 field was empty I would have this information in the letter: First_Lastname Adr1 ZipCity Is it possible to do so in the Report? -- Best Regards from Joergen Bondesen On Fri, 18 Jan 2008 23:13:10 +0100, Joergen Bondesen wrote: > Hi NG. > > I have a Table - Quiery an a Report. > The Reports contain fields: > > First_Lastname > Adr1 > Adr2 > ZipCity > > If this was mailmerge in Word and Adr2 field was e...

Text in formula?
What a pain in the neck to come back home after being away and not being able to access the newsgroups via my newsreader (http:// www.gmayor.com/MSNews.htm). Anyway ... <g> I have this formula in cell A16: ="Don't take today: " &+A15+1 I got it from googling for text and formulas in same cell. I've tried formatting cell as general and text but nothing comes out right. The result in A16 should say: Don't take today: Tue.Sep.14.2010 but instead it says: Don't take today: 40435 Can anyone advise how to fix this? Thanks! :oD On Mon, 30 Aug 2010 ...

Support for custom look up fields
I am dissappointed to learn the MS CRM 3.0 do not support custom made look up fields at a more profound level. I do acknowledge the work done on solving some of the issues concerning relationships between accounts and contact etc. through the relationship entity , but this solution still seems much more complex to manage in comparison to the possibility of simply creating and adding custom look up fields to your forms and thereby managing relationships - just the same way as pointing out a "primary contact" person. ---------------- This post is a suggestion for Microsoft, and ...

Current Date 01-20-10
I would like to filter on todays date. What is the field name for the current date. Thanks. Harry Try "Current Date." - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I would like to filter on todays date. What is the field name for the > current date. Thanks. > > Harry > ...

Scorekeeping Formula
Does anyone have a formula set up scorekeeping in Excel for the game Rummikub. We have tournaments and want to use the computer to keep track of scores, with minus scores added to the winners' scores. Thanks DocT ...

How Excel converts % sign in a cell and use in formula
I am trying to figure out a formula in Excel that I know is simple, but it's been years.... =(B3+(B3*$F$8))*(1+$F$9) I understand about the order of operations and what has to happen first and the absolute cell reference. I'm confused about what the 1 is in the (1+$F$9)...the info in cell F9 is sales tax and is written as 8.25%. Thanks in advance for the help. -- Peggy Duncan, Author Conquer Email Overload with Outlook http://www.PeggyDuncan.com Worksmart wrote: > I am trying to figure out a formula in Excel that I know is simple, but it's > been years.... > > ...

repeating formulas by the same row or colum increment as the last.
I need to repeat formulas in a sheet by the same #of rows. I mean I might increase the copied value by 22 rows. How can I enter that without typing each row increment ...

Formulas #43
I have a cell with a simple currency formula in it. I want to add a specific dollar amount to the formula result IF a single value from a group of 5 values appears in another cell. -- Thank you. John Q. Without more details, maybe something like: =A1+IF(OR(B1={"a","b","c","d","e"}),22,0) John Q wrote: > > I have a cell with a simple currency formula in it. I want to add a specific > dollar amount to the formula result IF a single value from a group of 5 > values appears in another cell. > -- > Thank you. John Q. -...

need help with Index, Match and Countif in the same complicated formula
Hi, I have a spreadsheet Expense Journal designed for entering expenses in two currencies, one for Dollar and one for a local currency. It has two sections of rows, the top is for Dollar Expenses and uses no exchange figures because the spreadsheet converts everything to Dollars. This Dollar section has five columns: Date Project # Account # Description Dollar Amount In the blank spreadsheet that they start anew each quarter, there are only two rows setup for Dollar expenses because most of their expenses will be in Local currency. Below the Dollar secti...

How to apply a formula accross all the rows when the total rows are too much in no. ?
Hi, guyz i know you can drag the formula accross the rows in that column t have that formula in effect in every cell. but i have some data lik 5000 rows then how to get that formula accross each cell, its painfu holding the mouse and dragging the formula accross the rows. please advise thanks in advance sorab -- Message posted from http://www.ExcelForum.com Hi after inserting this formula in the first row double click on the lower right corner of your cell selection -- Regards Frank Kabel Frankfurt, Germany > Hi, > guyz i know you can drag the formula accross the rows in that col...

Date an time on WinXP.
Does anyone know where windows Xp gets the time and date it displays from? My system date and time often fails, resulting in the time beiing 5-5mn delayed compared to what it shoul have been . I wonder what can cause that???? ...

Importing CSV file field mapping
When importing a CSV file for contacts or leads: How can I expand the drop down list of field choices under the Microsoft CRM fields to included fields such as Account, alternative address information, etc.? The current limited amount of fields keeps me from importing information currently contained within our Outlook based contact system. What I do to expand the number of fields exported from Outlook is to Export the contact records from Outlook to MS Access, then export the access contact table as a CSV file. This process gives me more information to import (i.e., other addres...

Formulas to run offline in 3.0?
Hi CRM Pros I have a prospective 3.0 customer. Basically they have a not too complex Excel sheet, that they would like to replace with CRM functionalty instead, in order to run it offline with automatic sync to the CRM server. I know that I can customize CRM 3.0 with extra fields on the oppertunity entity and with custom exchange rate and freight rate tables. But what are the possibilities in 3.0 of creating calculations to run automatically, e.g., tallying the sum of some of the custom fields, or for multiplying some of them together with the exhange rate from a separate table? These calcula...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

in formula a range cell reference eg) $A1:$A20 that is static when filling
if i want a cell reference to not increment when filling i put a dollar sign in front of it i noticed this does not work when you specify a range of cells eg ) $A1:$A20 although it is not an error when i fill in a formula it still increments the cell reference in each row but if i specify a single cell it does not. does anyone know the syntax for this, it's hard to word so i havent been able to find anything on google. thanks for your help! cheers, /sh You mean like this: =SUM($A$1:$A$20) ? -- HTH, RD --------------------------------------------------------------------------- P...

Another question regarding Dates..
First, thanks to the replies to my previous post. How can I work out the day & month when I only know the Year and Day Number? Ie Year = 2004 day number = 302 The answer I'm looking for is 28/10/2004 Again, taking into consideration the leap years Thanks again in advance =DATE(2004,1,302) Regards, Peo Sjoblom "Anthony Slater" wrote: > First, thanks to the replies to my previous post. > > How can I work out the day & month when I only know the Year and Day Number? > > Ie > > Year = 2004 > day number = 302 > > The answer I'...

Date format 03-03-08
How do I get a date to print as mm-dd-yy in a field? On Sun, 02 Mar 2008 23:43:02 -0500, BC wrote: > How do I get a date to print as mm-dd-yy in a field? Set the format property of the Date control to: mm-dd-yy -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...

formula #59
i am trying to do this:: =IF(J48<25,"",K48) just insert the info from I48 into K48 BUT =IF(J48>25) then add J48 info to I48 and place the total in K48. is this possible and how is it done =if(j48<25,"",if(j48>25,j48+i48)) -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947 View this thread: http://www.excelforum.com/showthread.php?threadid=394739 A formula can only enter values into the cell in which it resides, it cannot enter data int...

Adding a prefix to a cell by using a formula
Is there a formula that I can apply to a specific cell, so that when information it typed into that cell, it will automatically add a set prefix ? I have a form I designed in Excel that will be emailed to other people. One of the fields that they need to fill in is an email address. I do not want this turning into a hyperlink. I know how to disable the hyperlink on the PC I'm working on , but when the form gets emailed to others, the disable function is lost. If you type in a " ' " before the email address, it will not turn into a hyperlink. Is there a way to set...