Calculate time to a deadline

[Rob Kuijpers]

Hi all,

Is there a way to calculate the time between now and a day/time next
week which is dynamic?

For instance I have a deadline for a magazine next wednesday at 10:00
am. In a userform I would like to show the time to the next deadline
(always the next wednesday at 10). No dynamic time, just static when
the form is initialised. So a hard coded date for next wednesday is
out of the question.

I did a simular thing to show the next date when the magazine was due:
It's always the next friday. And when the deadline (wednesday 10:00)
has gone it's friday next week:

Dim DStr, WStr, Plus
If Format(Date, "dddd") = "thursday" Then Plus = 8
If Format(Date, "dddd") = "friday" Then Plus = 7
If Format(Date, "dddd") = "saturday" Then Plus = 6
If Format(Date, "dddd") = "sunday" Then Plus = 5
If Format(Date, "dddd") = "monday" Then Plus = 4
If Format(Date, "dddd") = "tuesday" Then Plus = 3
If Format(Date, "dddd") = "wednesday" Then
If Format(Time, "hh:mm") > "10:00" Then Plus = 9
Else
Plus = 2
End If

DStr = Format(Date + Plus, "dddd d mmmm yyyy")
WStr = Format(Date + Plus, "dd-mm-yyyy")
txtWeekMag = Format(WStr, "ww", vbMonday, vbFirstFourDays)
txtDateMag = DStr + " = week " + txtWeekMag.Value

So txtWeekMag shows the weeknumber of the next magazine
txtDateMag shows the next due date of the magazine

My problem is how the put next wednesday 10:00 am in a variable.

Any suggestions?

TIA,
Rob

 

[Helmut Weber]

Hi Rob,
calculating time is a special challenge.
I'm seeing no need to store "next wednesday 10:00".
I leave formatting of seconds left,
or splitting it up in days, hours, minutes, to you.

Sub Makro3()
Dim dDat As Date
dDat = Date
If Weekday(Date, vbMonday) = 4 And _
Timer < 36000 Then ' it's before 10 o'clock on wednesday
MsgBox "hurry up"
Exit Sub
End If
While Weekday(dDat, vbMonday) <> 4
dDat = dDat + 1
Wend
dDat = dDat & " 10:00:00"
MsgBox "seconds left : " & DateDiff("s", Now, dDat)
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/