Adding months

  • Thread starter Thread starter Eliezer
  • Start date Start date
E

Eliezer

I'm trying to get excel to realize that 1/26/04 + 6 mos =
(whatever). I can't figure out how to do it, though. When
I do =1/26/04+6, I get 01/06/00. When I do =1/26/04+6/0/0,
it gives me a divide by 0 error. Any ideas? Thanks in
advance!
 
Eliezer said:
I'm trying to get excel to realize that 1/26/04 + 6 mos =
(whatever). I can't figure out how to do it, though. When
I do =1/26/04+6, I get 01/06/00. When I do =1/26/04+6/0/0,
it gives me a divide by 0 error. Any ideas? Thanks in
advance!
Click to expand...


Dates are stored as days since 31 Dec 1899. So, just adding 6 to a date will
add 6 days.

Suppose your original date is in A1. This formula will add 6 months:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))
 
=date(year(A1),month(A1)+6,day(A1))

But do think about you want the result to be if A1 is Aug 31.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi
one way:
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

but this can cause problems if A1 is the end of a month. A more
generic/robust version would be
=DATE(YEAR(A1),MONTH(A1)+addmonths,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(
A1)+addmonths+1,0))))
repalce addmonths with 6 in your example

HTH
Frank
 
A shorter version of this formula would be:
=MIN(DATE(YEAR(A1),MONTH(A1)+6+{1,0},DAY(A1)*{0,1}))
 
Thanks to both of you... however, the reason I posted the
formula the way I did is because I'm not trying to
reference a date and create a new one. What I have is a
database with dates of Patient visits, and when someone
comes I change his date to the next visit, which could be
anything from next week to six months from now. I am
looking for a fast way to add months to a date. (1/1/4+7
is a fast way to add a week, so I'm looking for a fast way
to add months.) If this doesn't exist, oh well. I was just
looking to see if any of the experts here know of a way.
Thanks - Eliezer
 
Hi Eliezer!

For next appointment purposes you might find that adding 26 weeks is
better than adding 6 months. The reason for this is that you would
then get the appointment on the same day of the week. With a monthly
addition, the appointment might fall due on a weekend and that might
not suit either doctor or patient.

So that becomes A1+(7*26)

Also be a tad careful regarding the entry of you "now". If you use the
NOW or TODAY functions, you'll find that your next appointment will
change as the date changes. So input you "now" date using Ctrl+; or do
it manually using a system recognised by Excel as a date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I had the same problem so i wrote this little vba function that you ca
use. Its pretty straight forward, not the best bit of code I have eve
written, but it works:


PHP code
-------------------
'adds a certain number of months to a date
Public Function addMonthsToDate(dteDate As Date, noMonths As Integer) As Date
Dim i As Integer

For i = 1 To noMonths
If Month(dteDate) = 12 Then
dteDate = CDate(Day(dteDate) & "/1/" & Year(dteDate) + 1)
Else
dteDate = CDate(Day(dteDate) & "/" & Month(dteDate) + 1 & "/" & Year(dteDate))
End If
Next i
addMonthsToDate = dteDate

End Function
 
Back
Top