How to show only Working Day on expire date (Friday + 2 = Tuesday)

**alexnkc** · 02-18-2013, 07:01 AM

Hi,

I have a code which will show the today date and expire date in msgbox by click the button in attach excel file (and the code was show below ).
But how to make msgbox show only working day at expire date?

Example,
Today date: now (assume to be Friday).
By adding 2 to today date, the expire date will show on Sunday.

Prefer (How to

By adding 2 to today date, the expire date will show on Tuesday (instate of Sunday).

PHP Code:


Sub adding() 
 
Dim idate As String 
Dim result As String 
 
 
idate = Now 
result = Now + 2 
 
MsgBox "Today date: " & Format(Now, "dddd, dd / mmm / yy") & vbNewLine & _ 
                    "Expired date: " & Format(result, "dddd, dd / mmm / yy") 
 
 
End Sub

BR
KC

**:) Sixthsense :)** · 02-18-2013, 07:15 AM

Try this....

Please Login or Register  to view this content.

**nathansav** · 02-18-2013, 10:39 AM

or you can use the WORKDAY function.

**alexnkc** · 02-19-2013, 12:30 AM

Hi

Sixthsense

and nathansav,

Both your methods is work. I have join up / modified the codes to suit my need.
By adding another criteria:
1) dtExpired = worksheetfunction.workday(now,x)
2) x = activesheet.cells(10,5)

The expired date will showing working day (and during generating the expired date, it will skip through all the weekend and give more accurate ending/expired date)

PHP Code:


Sub adding() 
 
Dim x As String 
Dim dtExpired As String 
 
x = ActiveSheet.Cells(10, 5) 
 
dtExpired = WorksheetFunction.WorkDay(Now, x) 
 
Select Case Format(Date, "dddd") 
    Case Is = "Sunday" 
        dtExpired = dtExpired + 1 
    Case Is = "Saturday" 
        dtExpired = dtExpired + 2 
End Select 
 
MsgBox "Today date: " & Format(Now, "dddd, dd / mmm / yy") & vbNewLine & _ 
        "Expired date: " & Format(dtExpired, "dddd, dd / mmm / yy") 
 
End Sub