 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Adding 6 months to any given date
is it possible to add 6 months to a given date? i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date plus 6 months ie 04/12/03. the only way ive been able to get something near is by adding 182.5 which is half a year in days but this obviously doesnt account for the different months having varying amounts of days. Any Ideas? Regards Hoyt You could try this: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))  SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== is it possible to add 6 months to a given date? i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date plus 6 months ie 04/12/03. the only way ive been able to get something near is by adding 182.5 which is half a year in days but this obviously doesnt account for the different months having varying amounts of days. Any Ideas? Regards Hoyt To add 6 months, but cater for that month having less days and not spilling-over, =MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1})) SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > is it possible to add 6 months to a given date? > i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the date [quoted text clipped - 7 lines] > > Hoyt Bob You always can type faster than me<bg> SignatureRegards Roger Govier > To add 6 months, but cater for that month having less days and not > spilling-over, [quoted text clipped - 17 lines] >> >> Hoyt That's because I don't type it. I have a library of stuff that I just cut and paste from in many instances <vbg> SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Bob > You always can type faster than me<bg> [quoted text clipped - 20 lines] > >> > >> Hoyt Thanks Bob, this works Brilliant. Regards Hoyt > To add 6 months, but cater for that month having less days and not > spilling-over, [quoted text clipped - 14 lines] > > > > Hoyt Hi If you have the Analysis Toolpak loaded, Tools>Addins>Analysis Toolpak then =EOMONTH(A1,6) Otherwise the formula recently posted by Bob Phillips =MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1})) which caters for the fact that adding 6 months to 31 March, returns 01 October and not 30 September when adding 6 months by the standard method. SignatureRegards Roger Govier > is it possible to add 6 months to a given date? > i.e. if in cell A1 the date is 04/05/03, then cell B1 should return [quoted text clipped - 10 lines] > > Hoyt Hope you can get Excel to do it as you've made a mistake doing it by hand :) 6 calendar months on would be 04/11/03. Nice solution using MIN( with array - must get used to using that more. > is it possible to add 6 months to a given date? > i.e. if in cell A1 the date is 04/05/03, then cell B1 should return the [quoted text clipped - 9 lines] > > Hoyt SignatureSteve (3)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.