Set appointment time

  • Thread starter R0xIRUMtQkxT
  • Start date Views 1,978
R

R0xIRUMtQkxT

#1
I have created a macro that schedules an appointment for a follow up based on

the current date and time. The follow up has to be scheduled for the next day

at 8 a.m. I'm not sure about the best way to do that. I can only get it to

except a specific date and time and not a variable date (one dat from the

current date) and a specific time. Here is what I have that schedules a

follow up one day from the current date two hours ahead.

olAppt.Start = Now() + (1#) + (2# / 24#)

But if it is Thursday then I need it to schedule it one day from the current

date at 8 a.m. I've tried something that looks like this, and slight

variations, but it doesn't work. The only part that doesn't work is the

#8:00:00# AM part.

If Weekday(dteNextDate) = 5 Then

olAppt.Start = Now() + (1#) & #8:00:00 AM#

Any suggestions? Is it even possible?
 
S

Sue Mosher [MVP]

#2
Now() returns the current date/time. Date() returns the current date, with a

time of midnight. Use Date() with DateAdd() to return tomorrow's date with a

time of 8 a.m.

DateAdd("h", 8, Date+1)

You're on the right track with Weekday() if you need to adjust for weekends.

Sue Mosher

"GLHEC-BLS" <GLHECBLS> wrote in message

news:DB4E87CE-2AAB-4474-A512-50FA11082953@microsoft.com...
> I have created a macro that schedules an appointment for a follow up based
> on
> the current date and time. The follow up has to be scheduled for the next
> day
> at 8 a.m. I'm not sure about the best way to do that. I can only get it to
> except a specific date and time and not a variable date (one dat from the
> current date) and a specific time. Here is what I have that schedules a
> follow up one day from the current date two hours ahead.

> olAppt.Start = Now() + (1#) + (2# / 24#)

> But if it is Thursday then I need it to schedule it one day from the
> current
> date at 8 a.m. I've tried something that looks like this, and slight
> variations, but it doesn't work. The only part that doesn't work is the
> #8:00:00# AM part.

> If Weekday(dteNextDate) = 5 Then
> olAppt.Start = Now() + (1#) & #8:00:00 AM#

> Any suggestions? Is it even possible?
 
R

R0xIRUMtQkxT

#3
That is awesome! Works great thanks a lot. I can't believe I couldn't find

anything in the help section about Date() or Dateadd(). I still can't. Oh

well, that's what this discussions are for I guess.

Just one more question for my own curiosity. What does the "h" do? It

wouldn't work wihtout it, but I'm not sure what purpose it serves.

P.S. The weekday() works great for skipping weekends if it is Friday. It's

also what I use for the code you helped me with because if it is Thursday I

need to schedule the folow up for the next day (friday) at 8 a.m.

Thanks again!

"Sue Mosher [MVP]" wrote:


> Now() returns the current date/time. Date() returns the current date, with a
> time of midnight. Use Date() with DateAdd() to return tomorrow's date with a
> time of 8 a.m.

> DateAdd("h", 8, Date+1)

> You're on the right track with Weekday() if you need to adjust for weekends.

> > Sue Mosher
> > >

> "GLHEC-BLS" <GLHECBLS> wrote in message
> news:DB4E87CE-2AAB-4474-A512-50FA11082953@microsoft.com...
> >I have created a macro that schedules an appointment for a follow up based
> >on
> > the current date and time. The follow up has to be scheduled for the next
> > day
> > at 8 a.m. I'm not sure about the best way to do that. I can only get it to
> > except a specific date and time and not a variable date (one dat from the
> > current date) and a specific time. Here is what I have that schedules a
> > follow up one day from the current date two hours ahead.
> > olAppt.Start = Now() + (1#) + (2# / 24#)
> > But if it is Thursday then I need it to schedule it one day from the
> > current
> > date at 8 a.m. I've tried something that looks like this, and slight
> > variations, but it doesn't work. The only part that doesn't work is the
> > #8:00:00# AM part.
> > If Weekday(dteNextDate) = 5 Then
> > olAppt.Start = Now() + (1#) & #8:00:00 AM#
> > Any suggestions? Is it even possible?


>
 
S

Sue Mosher [MVP]

#4
The easiest way to access Help in VBA is to put the cursor on the function,

method, or property you're interested in and then press F1. What "h" means

will be spelled out once you look at the Help topic for DateAdd(), but see

if you can figure it out for yourself: What might "h" and 8 have in common

with your desired time?

Sue Mosher

"GLHEC-BLS" <GLHECBLS> wrote in message

news:983ED1A6-B38E-4E7A-A891-B05651A9E61B@microsoft.com...
> That is awesome! Works great thanks a lot. I can't believe I couldn't find
> anything in the help section about Date() or Dateadd(). I still can't. Oh
> well, that's what this discussions are for I guess.

> Just one more question for my own curiosity. What does the "h" do? It
> wouldn't work wihtout it, but I'm not sure what purpose it serves.

> P.S. The weekday() works great for skipping weekends if it is Friday. It's
> also what I use for the code you helped me with because if it is Thursday
> I
> need to schedule the folow up for the next day (friday) at 8 a.m.

> Thanks again!

> "Sue Mosher [MVP]" wrote:
>
> > Now() returns the current date/time. Date() returns the current date,
> > with a
> > time of midnight. Use Date() with DateAdd() to return tomorrow's date
> > with a
> > time of 8 a.m.
>

>> DateAdd("h", 8, Date+1)
>

>> You're on the right track with Weekday() if you need to adjust for
> > weekends.



>

>> "GLHEC-BLS" <GLHECBLS> wrote in message
> > news:DB4E87CE-2AAB-4474-A512-50FA11082953@microsoft.com...
> > >I have created a macro that schedules an appointment for a follow up
> > >based
> > >on
> > > the current date and time. The follow up has to be scheduled for the
> > > next
> > > day
> > > at 8 a.m. I'm not sure about the best way to do that. I can only get it
> > > to
> > > except a specific date and time and not a variable date (one dat from
> > > the
> > > current date) and a specific time. Here is what I have that schedules a
> > > follow up one day from the current date two hours ahead.
> >> > olAppt.Start = Now() + (1#) + (2# / 24#)
> >> > But if it is Thursday then I need it to schedule it one day from the
> > > current
> > > date at 8 a.m. I've tried something that looks like this, and slight
> > > variations, but it doesn't work. The only part that doesn't work is the
> > > #8:00:00# AM part.
> >> > If Weekday(dteNextDate) = 5 Then
> > > olAppt.Start = Now() + (1#) & #8:00:00 AM#
> >> > Any suggestions? Is it even possible?

>

>
>>
 
R

R0xIRUMtQkxT

#5
Yeah, I figured out that 'h" meant hour right after I posted the question.

Sort of felt silly. I did find it in the help section too. I have used

modified versions of it already in other areas as well. I was able to make a

combobox where the user could select the time they wanted to make the

follow-up, which is ten times better than my original idea. Here is what it

looks like, out of context:

ElseIf ComboBox2.Value = "9 a.m." Then

If Weekday(Now) = 6 Then

olAppt.Start = DateAdd("h", 9, Date + 3)

Else: olAppt.Start = DateAdd("h", 9, Date + 1)

End If

Thanks for the tip on the help feature. That should save me some time

searching fro solutions. You have been extremley helpful! Thanks again.

"Sue Mosher [MVP]" wrote:


> The easiest way to access Help in VBA is to put the cursor on the function,
> method, or property you're interested in and then press F1. What "h" means
> will be spelled out once you look at the Help topic for DateAdd(), but see
> if you can figure it out for yourself: What might "h" and 8 have in common
> with your desired time?
> > Sue Mosher
> > >

> "GLHEC-BLS" <GLHECBLS> wrote in message
> news:983ED1A6-B38E-4E7A-A891-B05651A9E61B@microsoft.com...
> > That is awesome! Works great thanks a lot. I can't believe I couldn't find
> > anything in the help section about Date() or Dateadd(). I still can't. Oh
> > well, that's what this discussions are for I guess.
> > Just one more question for my own curiosity. What does the "h" do? It
> > wouldn't work wihtout it, but I'm not sure what purpose it serves.
> > P.S. The weekday() works great for skipping weekends if it is Friday. It's
> > also what I use for the code you helped me with because if it is Thursday
> > I
> > need to schedule the folow up for the next day (friday) at 8 a.m.
> > Thanks again!
> > "Sue Mosher [MVP]" wrote:
> >
> >> Now() returns the current date/time. Date() returns the current date,
> >> with a
> >> time of midnight. Use Date() with DateAdd() to return tomorrow's date
> >> with a
> >> time of 8 a.m.
> >
> >> DateAdd("h", 8, Date+1)
> >
> >> You're on the right track with Weekday() if you need to adjust for
> >> weekends.

>
> >
> >> "GLHEC-BLS" <GLHECBLS> wrote in message
> >> news:DB4E87CE-2AAB-4474-A512-50FA11082953@microsoft.com...
> >> >I have created a macro that schedules an appointment for a follow up
> >> >based
> >> >on
> >> > the current date and time. The follow up has to be scheduled for the
> >> > next
> >> > day
> >> > at 8 a.m. I'm not sure about the best way to do that. I can only get it
> >> > to
> >> > except a specific date and time and not a variable date (one dat from
> >> > the
> >> > current date) and a specific time. Here is what I have that schedules a
> >> > follow up one day from the current date two hours ahead.
> >> >> > olAppt.Start = Now() + (1#) + (2# / 24#)
> >> >> > But if it is Thursday then I need it to schedule it one day from the
> >> > current
> >> > date at 8 a.m. I've tried something that looks like this, and slight
> >> > variations, but it doesn't work. The only part that doesn't work is the
> >> > #8:00:00# AM part.
> >> >> > If Weekday(dteNextDate) = 5 Then
> >> > olAppt.Start = Now() + (1#) & #8:00:00 AM#
> >> >> > Any suggestions? Is it even possible?
> >
> >
> >>


>
 
Top