Solved

# vbScript, Time difference in seconds until next week.

Posted on 2011-10-17
508 Views
``````Function TimeDifference (EndTime, EndDay)

'
' Msgbox the Time Difference between EndTime of a given week EndDay
' and Local Time Now
'
' End If
``````

Lets say that today's Local Day is "Tuesday" at 13:30

If I call the Function:

``````MsgBox TimeDifference ("15:30": "Tuesday")

it will Msgbox "7200" (seconds)
``````

Lets say that today's Local DayTime is "Tuesday" at 13:30

If I call the Function:

``````MsgBox TimeDifference ("15:30": "Friday")

it will Msgbox X (seconds) from the time now until15:30 on friday...
``````

0
Question by:New_Alex

LVL 44

Expert Comment

Look up the DateDiff function (f it's available to you).
0

LVL 44

Expert Comment

``````MsgBoxt DateDiff("s", Now, GetNextDate(vbTuesday, Now))

Public Function GetNextDate(ByVal pintDay As VbDayOfWeek, ByVal pdteGetNext As Date) As Date
'used to determine the next [passed day of week] from the current date

Do
Loop Until Weekday(pdteGetNext) = pintDay
GetNextDate = pdteGetNext

End Function
``````
0

LVL 5

Expert Comment

Function GetDateOfEndDay(EndDay) As Integer
Select Case UCase(EndDay)
Case "SUNDAY", "SUN"
GetDateOfEndDay = 1
Case "MONDAY", "MON"
GetDateOfEndDay = 2
Case "TUESDAY", "TUE"
GetDateOfEndDay = 3
Case "WEDNESDAY", "WED"
GetDateOfEndDay = 4
Case "THURSDAY", "THU"
GetDateOfEndDay = 5
Case "FRIDAY", "FRI"
GetDateOfEndDay = 6
Case "SATURDAY", "SAT"
GetDateOfEndDay = 7
Case Else
GetDateOfEndDay = 0
End Select
End Function

Function TimeDifference(EndTime, EndDay) As Long
Dim intWeekdayNow As Integer
Dim intHourNow As Integer
Dim intMinNow As Integer
Dim dblTimeNow As Double

intWeekdayNow = Weekday(Now)
intHourNow = Hour(Now)
intMinNow = Minute(Now)
dblTimeNow = TimeSerial(intHourNow, intMinNow, 0)

' Determined seconds from day to day (this will be the same time as Now)
If GetDateOfEndDay(EndDay) > intWeekdayNow Then
TimeDifference = GetDateOfEndDay(EndDay) - intWeekdayNow
Else
TimeDifference = 6
End If
TimeDifference = TimeDifference * 86400

' now add or subtract seconds depending on which is greater, given amount or Now

If TimeValue(EndTime) > dblTimeNow Then
TimeDifference = TimeDifference + ((TimeValue(EndTime) - dblTimeNow) * 86400)
Else
TimeDifference = TimeDifference - ((dblTimeNow - TimeValue(EndTime)) * 86400)
End If

End Function

Sub test()
MsgBox TimeDifference("14:45", "Sunday")
End Sub
0

LVL 5

Expert Comment

There's a bug (got a case of the mondays) -

' Determined seconds from day to day (this will be the same time as Now)
If GetDateOfEndDay(EndDay) >= intWeekdayNow Then
TimeDifference = GetDateOfEndDay(EndDay) - intWeekdayNow
Else
TimeDifference = 7 - (GetDateOfEndDay(EndDay) - intWeekdayNow)
End If
TimeDifference = TimeDifference * 86400
0

LVL 5

Accepted Solution

The full code should be:

Function GetDateOfEndDay(EndDay) As Integer
Select Case UCase(EndDay)
Case "SUNDAY", "SUN"
GetDateOfEndDay = 1
Case "MONDAY", "MON"
GetDateOfEndDay = 2
Case "TUESDAY", "TUE"
GetDateOfEndDay = 3
Case "WEDNESDAY", "WED"
GetDateOfEndDay = 4
Case "THURSDAY", "THU"
GetDateOfEndDay = 5
Case "FRIDAY", "FRI"
GetDateOfEndDay = 6
Case "SATURDAY", "SAT"
GetDateOfEndDay = 7
Case Else
GetDateOfEndDay = 0
End Select
End Function

Function TimeDifference(EndTime, EndDay) As Long
Dim intWeekdayNow As Integer
Dim intHourNow As Integer
Dim intMinNow As Integer
Dim dblTimeNow As Double

intWeekdayNow = Weekday(Now)
intHourNow = Hour(Now)
intMinNow = Minute(Now)
dblTimeNow = TimeSerial(intHourNow, intMinNow, 0)

' Determined seconds from day to day (this will be the same time as Now)
If GetDateOfEndDay(EndDay) >= intWeekdayNow Then
TimeDifference = GetDateOfEndDay(EndDay) - intWeekdayNow
Else
TimeDifference = 7 - (GetDateOfEndDay(EndDay) - intWeekdayNow)
End If
TimeDifference = TimeDifference * 86400

' now add or subtract seconds depending on which is greater, given amount or Now

If TimeValue(EndTime) > dblTimeNow Then
TimeDifference = TimeDifference + ((TimeValue(EndTime) - dblTimeNow) * 86400)
Else
TimeDifference = TimeDifference - ((dblTimeNow - TimeValue(EndTime)) * 86400)
End If

End Function

Sub test()
MsgBox TimeDifference("15:30", "Sunday")
End Sub
0

LVL 1

Author Closing Comment

Thank you sir.......

That worked Perfectly. Free Beer.....+ Extra Points for you !!!!

Thanks
0

## Featured Post

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.