Networkdays and Workdays aren't enough

Would this line of code added to your macro be what you are after?
It assumes your "date from" id housed in D7.
ActiveCell.FormulaR1C1 = "=WORKDAY(R7C4,-14)"

Any help?

Tom Urtis

This is where the problem kicks in.

If the data entered is 30/01/2002 then my first macr after 14 days gives 16/01/2002 - 14 full days later, but if the date entered is a weekend eg 27/01/2002 I want the macro to give the date 14 full days from then, but not a weekend; so the macro will instead of giving 13/01/2002 (Sunday), will give 11/01/2002 - the first available weekday before the weekend.

Hope this clarifies.
George

If this formula works, perhaps you could use the recorder for your macro
=(A1-14)-(WEEKDAY(A1-14)=7)-(WEEKDAY(A1-14)=1)*2

date of interest is in A1.

Good luck

Try this:

=IF(WEEKDAY(D7-14)=1,D7-13,IF(WEEKDAY(D7-14)=7,D7-15,D7-14)) This is where the problem kicks in. If the data entered is 30/01/2002 then my first macr after 14 days gives 16/01/2002 - 14 full days later, but if the date entered is a weekend eg 27/01/2002 I want the macro to give the date 14 full days from then, but not a weekend; so the macro will instead of giving 13/01/2002 (Sunday), will give 11/01/2002 - the first available weekday before the weekend. Hope this clarifies.

Here's some code that might help

As long as you are doing this with a macro, see if this does what you want.

Sub TimeFun()
If Weekday(Range("$A$1")) = 7 Then
ActiveCell.Value = Range("$A$1").Value - 15
ElseIf Weekday(Range("$A$1")) = 1 Then
ActiveCell.Value = Range("$A$1").Value - 16
Else
ActiveCell.Value = Range("$A$1").Value - 14
End If
End Sub

Tom Urtis I have a spreadsheet in which i am setting up 2 macros (never used before). I wish to enter a date in one cell and by pressing ctrl+L, the macro has given a date 14 days prior to this, but not a weekend date, in another cell.

Macro working BUT...

Using this code from Tom, slightly refined.

Sub Lothian()
'
' Lothian Macro
' Macro recorded 07/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+l
'
If WeekDay(Range("$F$2")) = 7 Then
ActiveCell.Value = Range("$F$2").Value - 15
ElseIf WeekDay(Range("$F$2")) = 1 Then
ActiveCell.Value = Range("$F$2").Value - 16
Else
ActiveCell.Value = Range("$F$2").Value - 14
End If

End Sub

Any ideas how to alter this so that if I enter another the date in Cell F2 the cell in C2 is updated and if another date is entered below F2 in F3, I can press Ctrl L to get the corresponding date in C3. This is meant to be a progressive thing. Forgot to mention that - sorry.

Thanks to all contributors.
George