Greetings,
I have three cells. The first (A1) has a month listing and the
second (B1) has a year listing. I have CommandButtons to change the
months and the years, just not the code to make them work.
Any help would be appreciated.
TIA
-Minitman
Greetings,
I have three cells. The first (A1) has a month listing and the
second (B1) has a year listing. I have CommandButtons to change the
months and the years, just not the code to make them work.
Any help would be appreciated.
TIA
-Minitman
Originally Posted by Minitman
Hello Minitman,
Excel stores all Time/Date inofrmation as numbers. Knowing that writing the code to advance a month or year isn't hard.
________________________________________________________________
CommandButton1_Click()
'Advance the Month
Dim newMonth, curDay, curYear, newDate
With Worksheets("sheet1").Range("A1")
newMonth = Str(Month(.Value) + 1)
curDay = Str(Day(.Value) )
curYear = Str(Year(.Value))
End With
'Convert the New Date String into a Date Value
newDate = CVDate(curDay &"-" & newMonth &"-" & curYear)
'Set Cell A1 to the New Date set to Next Month
Worksheets("Sheet1").Range("A1").Value = newDate
End Sub
-------------------------------------------------------------------------------------------------------
CommandButton2_Click()
'Advance the Year
Dim curMonth, curDay, newYear, newDate
With Worksheets("sheet1").Range("B1")
newMonth = Str(Month(.Value))
curDay = Str(Day(.Value))
newYear = Str(Year(.Value)+1)
End With
'Convert the New Date String into a Date Value
newDate = CVDate(curDay &"-" & curMonth &"-" & newYear)
'Set Cell A1 to the New Date set to Next Year
Worksheets("Sheet1").Range("B1").Value = newDate
End Sub
________________________________________________________________
Hope this helps,
Leith Ross
Minitman wrote:
>I have three cells. The first (A1) has a month listing and the
>second (B1) has a year listing. I have CommandButtons to change the
>months and the years, just not the code to make them work.
Is your question what to do with months > 12?:
NewYear = OldYear + AddYears + (OldMonth + AddMonths-1) \ 12
NewMonth = 1 + (OldMonth + AddMonths - 1) mod 12
or, if you've already got the old..+add..= new..
NewYear = NewYear + (NewMonth-1) \ 12
NewMonth = 1 + (NewMonth - 1) mod 12
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Thanks Dave,
I appreciate the reply, but that's not what I was looking for. It
turns out all I needed was to look deeper into the TEXT() command and
I came up with this where E3 is the date entry cell:
=TEXT(E3+30, "mmm")&" 20, "&TEXT(E3+30, "yyyy")
-Minitman
On Sun, 16 Jan 2005 20:39:32 -0800, Dave D-C <[email protected]>
wrote:
>Minitman wrote:
>>I have three cells. The first (A1) has a month listing and the
>>second (B1) has a year listing. I have CommandButtons to change the
>>months and the years, just not the code to make them work.
>
>Is your question what to do with months > 12?:
> NewYear = OldYear + AddYears + (OldMonth + AddMonths-1) \ 12
> NewMonth = 1 + (OldMonth + AddMonths - 1) mod 12
>or, if you've already got the old..+add..= new..
> NewYear = NewYear + (NewMonth-1) \ 12
> NewMonth = 1 + (NewMonth - 1) mod 12
>
>
>
>----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
>http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
>---= East/West-Coast Server Farms - Total Privacy via Encryption =---
=Text(Date(year(e3),month(e3)+1,20),"mmm d, yyyy")
might be another way.
--
Regards,
Tom Ogilvy
"Minitman" <[email protected]> wrote in message
news:[email protected]...
> Thanks Dave,
>
> I appreciate the reply, but that's not what I was looking for. It
> turns out all I needed was to look deeper into the TEXT() command and
> I came up with this where E3 is the date entry cell:
>
> =TEXT(E3+30, "mmm")&" 20, "&TEXT(E3+30, "yyyy")
>
> -Minitman
>
>
> On Sun, 16 Jan 2005 20:39:32 -0800, Dave D-C <[email protected]>
> wrote:
>
> >Minitman wrote:
> >>I have three cells. The first (A1) has a month listing and the
> >>second (B1) has a year listing. I have CommandButtons to change the
> >>months and the years, just not the code to make them work.
> >
> >Is your question what to do with months > 12?:
> > NewYear = OldYear + AddYears + (OldMonth + AddMonths-1) \ 12
> > NewMonth = 1 + (OldMonth + AddMonths - 1) mod 12
> >or, if you've already got the old..+add..= new..
> > NewYear = NewYear + (NewMonth-1) \ 12
> > NewMonth = 1 + (NewMonth - 1) mod 12
> >
> >
> >
> >----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
> >http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
> >---= East/West-Coast Server Farms - Total Privacy via Encryption =---
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks