Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Anyone have a better way to do this?
Here is what I am using currently:
=IF( NUM(WEEKDAY(MONTHEND(vDate)+1)) >4
, DATE(((MONTHEND(vDate)+1) - NUM(WEEKDAY(MONTHEND(vDate)+1)))+7)
, DATE(MONTHEND(vDate)+1) )
The 'vDate' is the variable assigned to an Input Box.
FYI, I have noted an error in my original calculation and have now corrected it. I also removed MONTHEND in favor of MONTHSTART. My question still stands though. Is there a better way to do this?
IF( NUM( WEEKDAY( MONTHSTART( vDate, 1 ) ) ) >4
, DATE( MONTHSTART( vDate, 1 ) + FABS( WEEKDAY( MONTHSTART( vDate, 1 ) ) - 7 ) )
, MONTHSTART( vDate, 1 ) )
Again, the 'vDate' is the variable assigned to an Input Box.
You could try this:
=Alt(Pick(Weekday(MonthStart(vDate, 1)) + 1, 0, 1, 2, 3, 4), 0)
Hey guys, did Mindaugas' post help you with this? If so, do not forget to come back to the thread and use the Accept as Solution button on it to give him credit for the assistance and to let other Community Members know it did work. If you are still working on things, leave an update on what you need.
Regards,
Brett
That solution assigns Saturday and Sunday as well as Monday as 0. I am having trouble understanding how this will get me to the first weekday of the next month based on the provided date.
if vDate = 1/20/2020 then the first weekday of the next month would be Monday 2/3/2020. Getting to the 2/3/2020 date is what I want to calculate.
FYI, I have noted an error in my original calculation and have now corrected it. I also removed MONTHEND in favor of MONTHSTART. My question still stands though. Is there a better way to do this?
IF( NUM( WEEKDAY( MONTHSTART( vDate, 1 ) ) ) >4
, DATE( MONTHSTART( vDate, 1 ) + FABS( WEEKDAY( MONTHSTART( vDate, 1 ) ) - 7 ) )
, MONTHSTART( vDate, 1 ) )
Again, the 'vDate' is the variable assigned to an Input Box.