VBA code for next business day

[NoxGalleon106]

Hi,

I am working on a macro wherein the condition is

If the values on the cells on column L is equal to the next business day(ie 02/11/2017) then
Activecell.offset (0,5)value= "TO PREMATCH"

Can you tell me the code for the next business day?

 

[Rick Rothstein]

You can get the next workday (as a date serial number) using this function call...

NextWorkday = Application.Workday(Date,1)

where you would put the actual starting date in place of the red highlighted word above.

 

[NoxGalleon106]

You can get the next workday (as a date serial number) using this function call...

NextWorkday = Application.Workday(Date,1)

where you would put the actual starting date in place of the red highlighted word above.

Thank you, Rick! But how can I set the date as the current date? We need to run the report on a daily basis and the date should be set as the current one to get the next business day .

 

[NARAYANK991]

Hi ,

In VBA , the Date function returns the system date.

https://www.techonthenet.com/excel/formulas/date_vba.php

 

[NoxGalleon106]

Hi ,

In VBA , the Date function returns the system date.

https://www.techonthenet.com/excel/formulas/date_vba.php

Thank you. Below is my code but I am getting an incorrect date format.

Sub try()
SD = Application.WorkDay(Date, 1)
MsgBox SD
End Sub

And I am getting a result of "43042" instead of a date format as dd/mm/yyyy

 

[NARAYANK991]

Hi ,

Excel treats dates as numbers. In any worksheet cell , you can enter the number 43042 and then format it as dd/mm/yyyy to see the same number displayed as 03/11/2017.

When you display the next business date using the MsgBox statement , you can use the Format function to display it the way you want to see it displayed :

Sub try()
SD = Application.WorkDay(Date, 1)
MsgBox Format(SD, "dd\/mm\/yyyy")
End Sub

 

[Rick Rothstein]

Thank you. Below is my code but I am getting an incorrect date format.

Sub try()
SD = Application.WorkDay(Date, 1)
MsgBox SD
End Sub

And I am getting a result of "43042" instead of a date format as dd/mm/yyyy

As I said in Message #2 , the code will return the next workday "as a date serial number". In VB and Excel, dates are stored as a floating point number (whole number are number of days since some initial starting date, fractions are the time represented as a fraction of a 24-hour clock). When you see a date in the format you are familiar with, that is just Excel or VB being kind to you and showing you what you want to see. When the formula (in Excel) or function (in VB) does not do that, you need to force the format upon the date serial number. In Excel, you simply use Cell Formatting, in VB you would have to use the Format function...

Sub Try()
  Dim SD As String
  SD = Format(Application.WorkDay(Date, 1), "dd/mm/yyyy")
  MsgBox SD
End Sub

Note that I formatted the date as a String variable... if you declared it as a Date variable, VB would simple convert it back to a floating point number again. Although I cannot speak to it as I live in the US and, hence, have not seen this problem for myself, but I have seen messages online that say when left to its own devices, VB forces the US format on a date (mm/dd/yyyy), not the date format for the computer's locale, so I guess it is best to force the display on the date only when you need to display it.