You need to format the cell as a date. Right click, format cells - select one of the date formats and 43040 will give you November 1st.
Hi,
I have seen many articles or posts on the internet that using the following formula, I could get the first workday of the month.
=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
However, the result of the formula is 43040. It doesn't make sense to me. Do I need to convert it first before I get say a day between 1st to 3rd?
Thank you!
You need to format the cell as a date. Right click, format cells - select one of the date formats and 43040 will give you November 1st.
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste
Excel dates are just numbers formatted to be dates. Format the cell as a date and you will see that 43040 is 11/1/17.
Colonel Sandurz: Prepare ship for light speed.
Dark Helmet: No, no, no, light speed is too slow.
Colonel Sandurz: Light speed, too slow?
Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.
Hi,
thank you!
it works.
I wanted use the formula in VBA. I tried thisbut somehow this is not working. Wonder if someone could help.Code:this_Month = application.WorksheetFunction.WorkDay(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1,1)
VB-wise, try this:
Code:Dim this_Month As Date this_Month = WorksheetFunction.WorkDay(Date - Day(Date), 1)
Adding to what Barry stated for the formula, in VBA try....
Code:Sub xxxx() Dim this_Month As Date this_Month = WorksheetFunction.WorkDay(WorksheetFunction.EoMonth(Date, -1), 1) Range("a1") = this_Month End Sub
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste
Like this thread? Share it with others