# VBA Message Box For First Business Day Of The Month

Nov 12, 2008

I am looking for vba code that will create a message box on the first business day of every month as a reminder.

## Calculate Date Of First Business Day Of A Month

Sep 19, 2012

I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.

For example:

I have a workbook with two worksheets in it.

The first worksheet is the input page.

On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.

Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12
Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)
(in this example, this should be 3) and displayed as 03-Sep-12
Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1

The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.

The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.

Daily
Date
USD

29 June 2012
1.0191

1 August 2012
1.0507

[Code] ........

Worksheet 1, Cell C1 should display the value of 1.0266

## Last Business Day Of Prior/Previous Month

Jun 12, 2008

what formula should I use to refer to the last working day on the prioir month. I have trie to look up forums but no result os yet.

## Calculate Future Dates On The Business Day Preceeding The 15th Of Each Month

Sep 16, 2009

Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..

## Display Message On First Of Every Month When Workbook Opens

Nov 28, 2012

Is there a way of displaying a message box on the first of every month when a workbook opens ?

## Display Current Month Working Days In A Message Box

Mar 5, 2014

I have a access table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button on the userform is pressed and it should check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).

## Message To Apear It The User Selects A Year AND Month Less Than The Current Year

Sep 25, 2006

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here

## Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under \$25K, recognize in next month (month N+ 1)
- \$25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over \$100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

Feb 26, 2014

How would I write a function to give me the last ten business dates moving horizontally from today's date with yesterdays date the being the last date? So if A1 is 2/26/14, how would I populate B1-B10 with wanting B10 to be 2/25/14 and moving backwards but only with business dates?

## Tat Calculation Only For Business Days

Dec 4, 2008

I am not able to calculate TAT(Turn around Time) between two dates without taking Off days. Plz help me on this.. File is attatched as per example..

## Return The Corresponding Business Week

Jan 15, 2009

I have a spreadsheet (attached) containing 3 columns, week commencing (c), week ending (d) and business week (e). The question is can a user enter a date in one cell (b4) and have the next cell (b5) return the corresponding business week, I have come up with a couple of solutions involving hidden columns but was wondering if there is a way to do all of this in one cell.

Mar 31, 2009

I am using the following formula to calculate business hours.

=(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00")

The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9. Now the problem is its calculating the correct value when the days are same, for e.g.,

Condition 1

When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00"

Condition 2

While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".

## Macro For Last Business Date?

Jan 30, 2014

create a macro?

I want to add the Last business date in B3 cell just by running a macro.

## Workbook Name Always Changes By (minus 2 Business Day)

Feb 13, 2012

My file name constantly changes, it will always be (minus 2 business dates from today)

Now when my macros run it goes between different worksheets so I need to tell it to go to right workbook. I tried below but getting an error 438 'Object doesnt support this property or method'

Code:

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate
Sheets("data").Select

## Business Day Calculation For All Rows

Aug 18, 2013

I have the following formula in a cell

=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))

its works off of cells A2 for the start date and cell B2 for the end date and works perfect

I want to use this in code to automatically count business days for all rows. I have tried the following

Dim ressfp,a4,a5
a4 = Worksheets("Master").Range("O" & b).Value 'target comp date
a5 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp =NETWORKDAYS(a4,a5)-(MOD(a4,1)>=MOD(a5,1))

I get an error on the word MOD saying compile error syntax error but cant work out why

i have tried replacing the networkdays formula with the following

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7))-(MOD(A2,1)>MOD(B2,1))

but this gives the error that the curly brackets are invalid characters.

Apr 23, 2008

I use this formula at work to calculate business hours from Mon-Fri:

=IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,\$N\$2:\$N\$23)-1)*(\$Q\$3-\$Q\$2)+IF(OR(ISNUMBER(MATCH(INT(L11),\$N\$2:\$N\$23,0)),WEEKDAY(L11,2)>5),\$Q\$3,MEDIAN(MOD(L11,1),\$Q\$3,\$Q\$2))-IF(OR(ISNUMBER(MATCH(INT(L10),\$N\$2:\$N\$23,0)),WEEKDAY(L10,2)>5),\$Q\$2,MEDIAN(MOD(L10,1),\$Q\$3,\$Q\$2)))

where Q3= business start time 8.30am
where Q2= business end time 5.30pm

thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours.

I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.

Jun 30, 2006

I am looking to calculate business days - more specifically Monday through Friday. I am not currently worried about holidays or vacations, yet I wouldn't mind including it if I could have a list to "check" from.

I tried previous searches, but found a few functions that I don't have on my computer.

## Calculate Due Business Day Date

Jan 9, 2007

I need to calculate a due date for the fifth business day of the next month following the input date. I am using Excel 2000.

Dec 18, 2009

I want to write a macro to add business hours to a datetime value, excluding weekends, non-working hours and holidays.

e.g., I have a value (datetime) of "18/12/2009 11:30:00 AM", I want to add 10 hours to this, so as to get the result as "19/12/2009 12:30:00 PM".

## IF Statements To Show Business Quarter?

Mar 11, 2014

I have a spreadsheet that tracks all field equipment inspections. This year, certain equipments will need to be inspected twice a year. Ideally, if it was inspected in Quarter 1, it needs to be inspected again in Quarter 3 and if it's Q2 then it'll be inspected again in Q4.

I need a formula that will tell me when the next inspection is due.

This is what I have so far:
Column I (Formula): date of last inspection
Column J (manual entry): "yes" for equipment that will be inspected semi-annually
Column K (formula): The Quarter is was inspected based on Column I's date
Column LThe quarter the next inspection is due

Oct 12, 2009

When i open a new thread, so here i am doing so at the advice of him. The message below is specific to him to reply to but for anyone else that might want to help me with this set of formulas to resolve my worksheet headache and lack of skills in excel. Please feel free to have a look at the worksheet i posted and the comments in each color coded cell.

Dec 7, 2008

I am looking for a function or formula that will return the date of the first Monday in any given month and year.

Sep 1, 2008

writing a formula that would result in my organizations' business fiscal year.

Assuming the fiscal year is 2008, the quarters are as follows:

1st QTR = 7/2007 - 9/2007
2nd QTR = 10/2007 - 12/2007
3rd QTR = 1/2008 - 3/2008
4th QTR = 4/2008 - 6/2008

When 7/1/2007 is entered, the result should be Q1-2008
10/20/2007, Q2-2008 and so forth.

Jan 7, 2014

I am trying to set up a spreadsheet to record business costs, I have a column [A] and [B] with start times and finish times respectively. [C] is the shift length say 10 hours but I always get an error if its a night shift as the start time is invariably later than the finish time. Is there a way around this without inputting the dates into the cells as I am trying to not duplicate data entry.

## Computing Business Hours Between Dates?

Jan 9, 2014

computing the number of business hours between 2 dates.

Given the following information:
- Business Hours: 8am -5pm (8:00-17:00)
- if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.

Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1
scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2

## Formula To Generate 3-5 Business Days

Mar 28, 2008

we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week.

What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded.

Something like this:

March w/ Sat w/o Sat Alaska
1 5 5 7
2 5 5 7
3 6 6 8
4 7 7 10
5 8 10 11

Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.

## Userform VBA Which Will Determine Business Days

Feb 18, 2009

I have created a useform in VB which will determine business days. I need help in the coding part.

In my user form I have text box, I have a calender and I have two command buttons, 1 Calculate 2 exit. What i would like to achieve is when i type a number in the txtbox and press cmd button calculate the code will shade the the date in the calender so for example if I type in 10, then the code should shade 13/02/09 on the calender since 13/02/09 is the 10th business day.

Sep 16, 2006

I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.

## Calculate Average Weekly Income For Business?

Mar 3, 2014

I have created a table for a business to monitor all income and expenses within a financial year of that business. This table contains raw data for example, date, income/expense, wholesale amount, retail amount etc. So when the business makes a sale for example I enter in the date of the sale, type of sale, wholesale and retail amounts etc, and i do this for every sale I make.

What i need to do is figure out a way to calculate the average monthly and weekly income to date so as the financial year continues and i make more sales i will continue to enter more data into this table and it will automatically adjust to the new weekly or monthly average income. This way i always have an actual and live average for every cent the business has made as the year progresses and i can use this to budget for the business. I would also like to know what the income is for the current week or month. that way i can say for example on average the business makes \$4000 a week and in this week it has made say \$5000.

Dec 8, 2008

I am looking for a formula that looks at a date and could add business days to it.

for example:

If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08