Solved: returning the previous tuesday at 2pm

[p45cal]

While the likes of [vba]Date - Application.Weekday(Date, 2) + 8[/vba] 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]

[vba]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
[/vba]

[Sagy]

Might not be elegant, but should be easy to understand and maintain
[VBA]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[/VBA]

[p45cal]

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]

Elegance doesn't count for much if it doesn't work!
[VBA]
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
[/VBA]

[Bob Phillips]

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]

Thank you all for your help!