How to use Macro in my case Closed

Question

Hello, 

Would appreciate if anyone could help me in detail how to use macros in the below case:
I have Expected closure date Column A1, and escalated days Column B1- which needs to be find out.
Ex:   Column A includes-   22-Oct-11, 21-Oct-11, 24-Oct-11, 27-Oct-11, 28-Oct-11,31-Oct-11
        In Column B Answer is coming as on 28th Oct 2011, B1= (-6), B2= (-7), B3=(-4), B4=(-1), B5=(0) & B6=(3) . Because I have applied formula as =(A1-today()).

However the purpose of this sheet is to identify on daily basis that how many days are in hand to work on particular case as the case needs to get closed by the mentioned date on expected closure date column or how many days passed without actioning on particular case. The formula which i have used in B column is not helping me to find out the correct escalated days.

Because, 23rd Oct 2011 is Sunday and 26th Oct 2011 is Public Holiday, as these two days will not be working hence these days should be automatically excluded and the correct answer should come in B1 as (-4) instead of (-6). Likewise correct Answers should be as mentioned below:

B1(-4), B2= (-5), B3=(-3), B4=(-1), B5=(0), B6(2).

Need an early closure to this case as i need to start this sheet ASAP. Pls Help at the earliest.

Regards,

Ann.



Configuration: Windows XP / Firefox 7.0.1

venkat1926 · Answer

enter dates as excel dates for e.g 
10/24/11
10/28/11
etc
you can format it as you like 

see the configuration of dates(following above rules)

		10/26/2011(this is entered in C1)
the following entered in rows 3 and down in columns A and B
22-Oct-11	10/28/2011	(these two are in A3 and B3)
20-Oct-11	10/28/2011	
24-Oct-11	10/28/2011	
27-Oct-11	10/28/2011	
28-Oct-11	10/28/2011	
31-Oct-11	10/28/2011	

now in C3 enter this formula 
=NETWORKDAYS(A3,B3,$C$1)
note dollar signs for C1
copy C3 down.

venkat1926 · Answer

in b column the ACTUAL entry is 
=today() 
so that today"s date will be automatically entered in B column and the elapsed days will be available. 

if you do like this automatically the actual elapsed days will be shown in column C

venkat1926 · Answer

see help under neworkdays

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

weekends mean Saturdays also holidays.

today 29th
start date 22nd
omitted 22(sat),23(sun),26(holiday),29(sat)
workdays 24,25,27,28 (start days not taken) as this is interval.

venkat1926 · Answer

normally for an intersval start date is left and only today's date is taken.

2. what is your holiday's list.  is there a saturday or sunday in that list.

venkat1926 · Answer

do this experiment
enter data as follows in column A from A1

date
10/24/2011
10/27/2011
10/28/2011
10/31/2011
11/4/2011

In B1 enter    number of working days

holidays list enter from G1 to G12

no run this macro and test 

Sub test()
Dim r As Range, c As Range, interval As Long, j As Long, holidays As Range, choliday As Range
Set r = Range(Range("A2"), Range("a2").End(xlDown))
For Each c In r
interval = CDate(Date) - CDate(c)
'MsgBox interval
j = 0
Set holidays = Range("G1:G12")
For Each choliday In holidays
choliday.Select
If CDate(choliday) >= WorksheetFunction.Min(CDate(Date), CDate(c)) _
And CDate(choliday) <= WorksheetFunction.Max(CDate(Date), CDate(c)) Then j = j + 1
'MsgBox j
Next choliday
'MsgBox j
interval = interval - j
c.Offset(0, 1) = interval
c.Offset(0, 1).NumberFormat = "0"
j = 0
Next c
End Sub

the result is in B2 to B6

for retesting clear B2 to B6 and again run the macro 

give feedback please

venkat1926 · Answer

I am giving you the colums A and B after running macro

date	number of working days
10/24/2011	4
10/27/2011	2
10/28/2011	2
10/31/2011	0
11/4/2011  	-4

sorry for a few  spelling mistakes as I was hasty in forwarding the solution immediately as today is your target date.

venkat1926 · Answer

not clear what is your version of excel 2003(and earlier) or 2007
I assume it is 2003 or earlier

open a new excel file
select sheet1
enter the data as I have told you( I am repeating her for easy reference)
quote
enter data as follows in column A from A1

date
10/24/2011
10/27/2011
10/28/2011
10/31/2011
11/4/2011

In B1 enter number of working days

holidays list enter from G1 to G12 
unquote

open vb editor (click alt+F11)
click control+R
you will see all the open excel files
brig the cursor to the new file(it may look like Book1 or Book 2 etc)
now go to vb editor and click insert(menu) -module in the sub menu
a module page opens (perhaps module 1 and the file name )
there copy paste the macro 
save the file with some name in some folder
now go to sheet1
click tools(Menu)-macro-macros
in the macro window go down to "macros in"
click the arrow at the right end and choose "this workbook"
in the top part you will see the "test"
highlight "test" and click Run on the right side

you will see in column B the result
save the file

the question of posties as negatives and vice versa is only semantic. still if you are  particular I have modified the macro slightly 

Sub test()
Dim r As Range, c As Range, interval As Long, j As Long, holidays As Range, choliday As Range
Set r = Range(Range("A2"), Range("a2").End(xlDown))
For Each c In r
interval = CDate(Date) - CDate(c)
'MsgBox interval
j = 0
Set holidays = Range("G1:G12")
For Each choliday In holidays
choliday.Select
If CDate(choliday) >= WorksheetFunction.Min(CDate(Date), CDate(c)) _
And CDate(choliday) <= WorksheetFunction.Max(CDate(Date), CDate(c)) Then j = j + 1
'MsgBox j
Next choliday
'MsgBox j
interval = interval - j
c.Offset(0, 1) = -interval
c.Offset(0, 1).NumberFormat = "0"
j = 0
Next c
End Sub


It is this macro which you have to park not the old one

you yourself said in the first message
"how to use macros in the below case:"
I thought you know about the macros and you want a macro

is this clear. please give me feedback

venkat1926 · Answer

did you receive my last message how to run a macro in excel???????

venkat1926 · Answer

It is not B1 it is B2 at least in my sheet
now see the data in columns C and d
column C gives the number of days upto today
(the formula in B2
=today()-A2
which is copied down 
column D contains holidays as per G1 to G12

date	             wkdays	today-A	holidays      working days
10/24/2011	-5	           8	        26,27,30      25,28,29,31,1
10/27/2011	-3	          5	        27,30            start date is 28(27hday) so 29,31,1
10/28/2011	-3	          4	        30                 29,31,1
10/31/2011	-1	          1	        nil
11/4/2011    	3	          -3	       nil

the workdays has to be defined by you .  here it takes into account as interval 
example from yesterday today it is one day (not 2 days). if one of these (yesterday or/and today) are holidays it is zero days.

see the table above  table
say the row no. 2 . the elalpsed days from start date is 8 taking into account both holidays and non holidays.  there are three holidays in column D (3). so it is 8 minus 3=5 how do you get 6. so your definition is different from normal definition.  If you WANT to take include the start date in all case then add(or sutract) 1

some  points I am intrigued
1. how can start date be on a holiday. if the start date is holiday normally the next working day is taken as start date
2. what is the logic and meaning of elapsed days for a future date(11/4/11)
3. elapsed days from start date to today is only positive but according to your logic it is negative.

if you have a different definition you adjust column B 
if it is completely logical equally for all the days you can put a condition.

I hope above table is self explanatory.

Still I did not catch your negatives and positives. 
elapsed days is always from the start date to today and that will be positive.


Of course your logic  depends upon your problem and need not agree with my logic.

I suggest you  sit down for half an hour and think about your problem again and make a decision and modify the macro according to you.

venkat1926 · Answer

ARE WE NOT LOOKING AT CROSS PURPOSES
MY VALUES IN COLUMN B WAS calculated  based on the macro 



so every day you have to AGAIN RUN  the macro test.

when I ran the macro on 1 nov 2011 the data and result (col A and B are 

date	"number of 
working days"
10/24/2011	-5
10/27/2011	-3
10/28/2011	-3
10/31/2011	-1
11/4/2011  	3
is this not correct particular with respect to 11/4/11


formula networkdays(startdate,enddate,holidays) gives problem  I think somehow you have to use the macro I have given 
I have already told you how to use the macro.

venkat1926 · Answer

There are lot of discussions on networkingdays formula see 
http://dailydoseofexcel.com/archives/2004/07/19/networkdays/
see particualarly the comments.   The formula gives problem if the end date month is not the as the month of end date (going to the next month).
Besides networkdays formula is already taking Saturday and Sundays as holidays. the third argument (in your case g1 TO g12  should contain only holidays other than Sat/sun.  

particularly one of the comments is 

"The real issue is the function does not do what the function definition claims and apparently has been that way since day one. No mention is made on the knowledge base of the problem. It will be interesting to see if I get a response from Microsoft."

remember these comments were far back in 2004

so better use macro than this formula (or function)

Ann · Answer

Hi Venkat,

Thanks a ton, Venkat, for your outstanding timely support. What i came to a conclusion that with the help of Networkdays formula, am getting the correct escalated days. However i noticed that if any holiday is coming just next to the expected closure day that time it is giving one day extra in the escalated column. so with the help of the link which you have provided me, i understood that i can use +1 in the same formula. But these things i need to do some manual work.

Further am using Excel 2007.

With regards to usage of macros, am not clear on that part. As you said i need to run that on daily basis. And do i need to run the below mentioned macro:

Sub test()
Dim r As Range, c As Range, interval As Long, j As Long, holidays As Range, choliday As Range
Set r = Range(Range("A2"), Range("a2").End(xlDown))
For Each c In r
interval = CDate(Date) - CDate(c)
'MsgBox interval
j = 0
Set holidays = Range("G1:G12")
For Each choliday In holidays
choliday.Select
If CDate(choliday) >= WorksheetFunction.Min(CDate(Date), CDate(c)) _
And CDate(choliday) <= WorksheetFunction.Max(CDate(Date), CDate(c)) Then j = j + 1
'MsgBox j
Next choliday
'MsgBox j
interval = interval - j
c.Offset(0, 1) = -interval
c.Offset(0, 1).NumberFormat = "0"
j = 0
Next c
End Sub



On daily basis if i need to run the macro then if you could help me with the detailed steps which i need to follow in Excel 2007, i would be very grateful to you. 

Thanks & Regards,

Ann

Ann · Answer

Already i have one saved Macro enable Excel file where i have my base data. And i will have to find the escalated days in that same file. Hence would request you to kindly provide me the detailed step to run the macro on daily basis on the same file.

Please ref my previous comment also.

Thanks & Regards,

Ann

venkat1926 · Answer

I  have uploaded the file named "ann.xlsm"
you can download this file from this webpage

http://hotfile.com/dl/134006529/809ee4f/ann.xlsm.html

download and save the file 
in the file you can add if you want some more dates in column A below the existing dates without leaving any row  in column A blank.

This is a file in excel 2007 that too macro enabled file(the m at the end of xlsm is that - if the file does not have macro or not enable macro then the file name is .xlsx.

sometimes there may be a pop up message at the top to enable or disable macros.there will be a "options" button and if you click the two choices will come up. click "enable macros"

now I have already parked the macro in the module. If you want to see click Developer ribbon and click "macros" on extreme left side below the main menu.
at the bottom "macros in" you click the arrow and choose "this workbook"
you will see the names of the macro(s) in this file
highlight the name of the macro, in this case "test". you can click Run on the right.
macro test will be run.
But see below this is not necessary.

Now I have already made a provision that whenever you open the file the macro test will automatically run.  You need not do anything. you want how it is done the steps are 

click Developer ribbon
click vb editor on the extreme left below the ribbon
vb editor opens
click control+R
the list of the open excel files names are seen
bring the cursor to this file(in this case "ann.xlsm") and right click the item under this file name "this workbook".  right click "this workbook" and click view code 
you will see the following


Private Sub Workbook_Open()
test
End Sub

This means that whenever you open this file the macro "test" in the module will automatically run.
so you need not do anything. when you open the file in the morning the macro will be automatically  run. you can save the file. even if you close the file in the same day and open the macro will be run but because today is the same day  there will be no change in the result(in this case column B).
only when you open next day the result will change .

I hope I made myself clear. You are at liberty to ask any doubt or question. 
I wish there is some provision to know the email addresses of both of us but I realize that this is not allowed by the forum due to some valid reasons.

venkat1926 · Answer

when you click the webpage for downloading choose regular download (this is free). 
the other one is paid.

venkat1926 · Answer

in whichever solution you are comfortable with is the best solution for you. 

But if you are going to do lot with excel it is better to learn about macros. there are tutorials in the web and also good books.   get them for the version you are using.

best compliments and bye

How to use Macro in my case

16 responses

Similar discussions

Subscribe To Our Newsletter!