Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
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 , 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
Sat, 09 Jun 2007 08:13:15 +0530 from Sunayan Sanatani
: 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/ |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
In article ,
Stan Brown 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... |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula for date field
Sat, 09 Jun 2007 22:59:02 +0530 from Sunayan Sanatani
: 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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked date field in worksheet defaults a blank field as 1/0/1900 | Excel Worksheet Functions | |||
Converting a date field into a month-year only field | Excel Discussion (Misc queries) | |||
Changing a text field to a date field | New Users to Excel | |||
create formula. 1 field constant and another field varies by inpu. | Setting up and Configuration of Excel | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) |