macro to change date

[daveyc18]

hello,

a macro that changes the date in , say, A1, but i want the date to be the PREVIOUS business day. so today is april 12, i press the macro so it shows april 11. Or say on Monday, April 15, when I press the button the date shows Friday, April 12.

Thanks!

[Sean Thomas]

Hi, try this

you might want to put some extra code in to make sure selection is no more than 1 cell

Sub Weekday()
Dim TDate, YDate As Date
Dim NDate As Integer
TDate = Selection
NDate = WorksheetFunction.Weekday(TDate, 2) '("12/04/13")
Select Case NDate
    Case 7
    YDate = TDate - 2
    Case 2, 3, 4, 5, 6
    YDate = TDate - 1
    Case 1
    YDate = TDate - 3
End Select
Selection = YDate
End Sub

[daveyc18]

I get "run-time error '1004': application-defined or objected-defined error"

when i debug, "selection = ydate" is highlighted

[daveyc18]

I should also mention that it will adding one business day...but will be one business day before today, lol

so, for example, I open the file today...it shows "April 10"...and I want the date to change to April 11 (today is April 12, so one business day before)

format....MM/DD/YYYY

[Sean Thomas]

This works good for what you originally asked for

Sub Weekday()
Dim TDate, YDate As Date
Dim NDate As Integer
If Selection.Cells.Count > 1 Or IsEmpty(Selection) Then Exit Sub
TDate = Selection
NDate = WorksheetFunction.Weekday(TDate, 2) '("12/04/13")
Select Case NDate
    Case 7
    YDate = TDate - 2
    Case 2, 3, 4, 5, 6
    YDate = TDate - 1
    Case 1
    YDate = TDate - 3
End Select
Selection = Format(YDate, "MM/DD/YYYY")
End Sub

Make sure you have selected a cell before running macro.

the only way you can get the cell to contain todays date -1 when is to run another macro.
I would suggest placing one in this workbook module for workbook_open procedure

something like Sheet1.Range("A1"). value = today()

you cant use the worksheet function because the other macro will always overwrite it.