PDA

View Full Version : Solved: returning the previous tuesday at 2pm



p45cal
03-18-2009, 08:57 AM
While the likes of Date - Application.Weekday(Date, 2) + 8 will return next Monday's date, I'm looking for an elegant way, inputting any date and time, to return the previous occurrence of a Tuesday-at-2pm, be it VBA or a worksheet formula.
For clarity, given Tuesday 11 Aug 2009 13:59 it should return Tuesday 4 Aug 2009 14:00,
and given Tuesday 11 Aug 2009 14:01 (a time just 2 minutes later than above) it should return Tuesday 11 Aug 2009 14:00
regards,

mdmackillop
03-18-2009, 10:50 AM
Sub LastTues()
Dim Dte As Long, d As Long
Dte = DateValue(InputBox("Enter date and time" & vbCr & "Format dd/mm/yy hh:mm", , Format(Now(), "dd/mm/yy hh:mm")))
d = Dte - Weekday(Dte) + 3
MsgBox Format(d + 14 / 24, "dddd d mmm yyyy hh:mm")
End Sub

Sagy
03-18-2009, 11:25 AM
Might not be elegant, but should be easy to understand and maintain
Function LastTue1400(dDate As Variant) As Date
Const TwoPM = 14# / 24#
Dim dTemp As Date
Dim iWeekDay As Long

dTemp = Int(dDate - TwoPM) ' Reset to date only after going back 14 hours
iWeekDay = Application.Weekday(dTemp, 1)
If iWeekDay <= 2 Then ' Sunday or Monday, need tuesday of last week
dTemp = dTemp - iWeekDay - 4
Else ' other days, need Tuesday of this week
dTemp = dTemp - iWeekDay + 3
End If
LastTue1400 = dTemp + TwoPM
End Function

p45cal
03-18-2009, 12:13 PM
Thank you both. While mdmackillop's code has the elegance I was looking for, and was in the same vein as my own explorations, it doesn't seem to give the right results in the first two rows below:

Input Result
16/03/09 13:00 Tuesday 17 Mar 2009 14:00
17/03/09 13:59 Tuesday 17 Mar 2009 14:00
17/03/09 14:01 Tuesday 17 Mar 2009 14:00
19/03/09 13:59 Tuesday 17 Mar 2009 14:00
20/03/09 13:59 Tuesday 17 Mar 2009 14:00 (I suspect it's because Datevalue only returns date with no time element, and Dte has been dimmed as long anyway)
whereas Sagy's longer code seems to give the right results:

Input Result
16/03/09 13:00 Tuesday 10 Mar 2009 14:00
17/03/09 13:59 Tuesday 10 Mar 2009 14:00
17/03/09 14:01 Tuesday 17 Mar 2009 14:00
19/03/09 13:59 Tuesday 17 Mar 2009 14:00
20/03/09 13:59 Tuesday 17 Mar 2009 14:00 So.. can it be improved?

regards,

mdmackillop
03-18-2009, 01:21 PM
Elegance doesn't count for much if it doesn't work!

Sub LastTues()
Dim Dte, d As Long
Dte = InputBox("Enter date and time" & vbCr & "Format dd/mm/yy hh:mm", , Format(Now(), "dd/mm/yy hh:mm"))
Dte = DateValue(Dte) + TimeValue(Dte)
d = Int(Dte) - Weekday(Dte) + 3
If d + 14 / 24 > Dte Then d = d - 7
MsgBox Format(Int(d) + 14 / 24, "dddd d mmm yyyy hh:mm")
End Sub

Bob Phillips
03-18-2009, 01:34 PM
A formula just for fun

=INT(A2-WEEKDAY(A2,2)+2)-(AND(WEEKDAY(A2,2)<3,MOD(A2,1)<TIME(14,0,0)))*7+TIME(14,0,0)

p45cal
03-18-2009, 02:34 PM
Thank you all for your help!