if date falls on weekend, roll to Monday

[daveyc18]

eg if the maturity date was october 13, i'd want the maturity date to now be october 14 (monday).

 

[steve the fish]

Try this:

=WORKDAY(A1-1,1)

 

[daveyc18]

Try this:

=WORKDAY(A1-1,1)

for a date of 7/29/33, i'm getting 7/31/33, however 7/29 is a friday, not saturday or sunday. please advise.

 

[MARK858]

for a date of 7/29/33, i'm getting 7/31/33, however 7/29 is a friday, not saturday or sunday. please advise.

If you put the year as just 33 then the default in Excel/Windows will treat it as 1933 not 2033. The 29th July in 1933 was a Saturday.

See the link below

https://support.office.com/en-us/article/change-the-date-system-format-or-two-digit-year-interpretation-aaa2159b-4ae8-4651-8bce-d4707bc9fb9f

 

[Special-K99]

7/29/33 is being translated as 1933 not 2033.

7/29/1933 was a Saturday hence the rollover to Monday.
If you want 7/29/2033 you'll need to enter the date as 4 digits.

 

[daveyc18]

yes apolioguies guys

 

[daveyc18]

7/29/33 is being translated as 1933 not 2033.

7/29/1933 was a Saturday hence the rollover to Monday.
If you want 7/29/2033 you'll need to enter the date as 4 digits.

is there a macro that will do this for me? actually, ideally, i want to keep the year number displayed as two digits even after the conversion.

 

[Special-K99]

Not from me. I don't know enough about VBA.
Just format the cell to display the years as 2 digits.

 

[MARK858]

See Ricks response to the OP's duplicate (well strictly speaking this was the duplicate as they posted the other thread before post number 7) here

but the correct answer is still to enter the dates correctly i.e. 4 figures for the year and then format the cell