Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Count The Week Days Only In Excel


I had to create this function to count the week days (minus saturday and sunday) between to dates.

Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If Weekday(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

I then have to call it from a cell with: =GETWORKDAYS(startdate,enddate)

Is there a way to do it with a formula? I dont want to create the module in every worksheet I create. I have a lot of users that will use this function, so the simpler the better.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Week Numbers &amp; Week Days
I have a project where I need to find the week number ( say Jan 1st to Jan 6th is Week#1, Jan 7th to Jan 13th is Week#2)

also How can I find the day of the week, How do I find whether Jan 1st is Monday or Sunday.

Anybody ?

Excel - Calculating An End Date ( Option To Select Which Days To Count )
I've been playing around with a calendar based Date Calculator in Excel.
The problem I'am having is Calculating the End date based off information selected
and which days have been chosen to be counted.
I've attatched the spreadsheet. It's easier to look at than explain.
any ideas would be greatly appreciated!

Days Of The Week
Hi,

I want to run an application using the timer and the day of the week, I don`t want the application to be launched over the weekend,just monday to friday.


for example'

if Day<>saturday or Day <> sunday and time = 12.00 then launch program

else

end

I`m very new to VB so don`t know the code, the above is just my theory of what I would like. I would be very grateful if somebody could convert this to VB.

thanks in advance

jono

Days Of The Week
hi,
if today is 01/01/06, how can I be able to display the list of the 5 following days (01/02, 01/03, 01/04, 01/05 and 01/06), and the 7 days of week?
please help how!!!! Thank you,

Week Days
I'm wanting to compare two dates for example 01/06/04 and 17/06/04 and count the number of week days between those two days (excludes Saturday and Sunday). What would be the best way of doing this?

Days In Week
Well you can easily find the day of a specific date by doing this

VB Code:
'This returns 1 for sunday, 2 for monday, 3 for tuesday etc tempdate = Day(DateToCheck)


Does that help?

How To Get Week Days From A Date In Vb6
Dear,

I have to change the days of the week from satureday to sunday and sunday to satureday. Is there any function which can impliment it.

regards.

Flexgrid And Order Days Of Week Help...
hi all

I'm fairly new to VB, and I'm currently using this flexgrid and trying to create some code so that I can select a weekday from a combo box (i.e. Monday to Sunday)

I want to be able to select the day and then from that I want to order the rows of the flexgrid starting from the day selected from the combo box

e.g. If I select Thursday from the combo box I want to be able to click a button or something so that in the flexgrid the labels on the rows order as follows:

Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday

I hope from that you can understand what I'm talking about?

I've tried to start working out how to do this by loading the days into a listbox with this code in the command1 button...


Code:
List1.Clear
Dim x%
Dim q(0 To 6) As String

q(0) = "Monday"
q(1) = "Tuesday"
q(2) = "Wednesday"
q(3) = "Thursday"
q(4) = "Friday"
q(5) = "Saturday"
q(6) = "Sunday"

For x% = 0 To 6
If Combo1.Text = q(x%) Then
List1.AddItem q(x%)
Do While x% < 6
x% = x% + 1
List1.AddItem q(x%)
Loop
End If
' Also tried something in here to show all the other days, but it didn't seem to work no matter what I tried
Next

If someone could help me with some code with this, but so I can load it into a flexgrid using FG1.TextMatrix(>row<, >col<)

cheers all
treelovinhippie

Calculate Days Of The Week For A Given Month
I'm doing a project for school and needed help on figuring this one out. We have to come up with a program able to accept a year and a month. Then calculate the days for that specific year and month and place the information into a text box. I have the validations for the txt strings no problem. But how to go about retrieving dates? Gee wiz.... did you guys have this much trouble starting out? We went over in class the commands for year(date), day of week(date), and month(date). But basically all that was given was the command. We did one example for listing leap years for the past decade. Interesting to say the least. Please help!

Retrieve Dates For All 7 Days Through Week Number
Hi all.

I made the following on a vb form using labels and textbox controls (my apologise for not getting the alignment right down here!):

Week nr.:

Monday Tuesday Wensday Thursday Friday Saturday Sonday
Date
Hours 8 8 8 7 5 4 0

This will be used to enter the number of hours that an employee has works that week.

Question: how can I automaticaly let VB insert the right dates in the Date-column when a user has entered the week number?

Resolved VB Code FOr Calculating Working (week Days) Including A Holidays Table.
Hi All I am try to create a function which will calculate the net working days between two dates, not including public holidays. I borrowed code but I am unable to get it to work, and NetWorkingDays MS in built function does not work either. (holiday table has two columns ROWID, HolidayDate d/mm/yyyy

Here is the code can anyone please help.


Function WorkingDays2() As Integer

On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim Startdate As Date
Dim EndDate As Date

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
'holiday table has two columns ROWID HolidayDate dmmyyyy
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

Startdate = [tblEnquiries].[Date Received]
EndDate = [tblEnquires].[Date Completed]

intCount = 0

Do While Startdate <= EndDate

rst.FindFirst "[HolidayDate] = #" & Startdate & "#"
If Weekday(Startdate) <> vbSunday And Weekday(Startdate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

Startdate = Startdate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

 

Edited by - bmwtiger on 6/29/2005 11:35:35 PM

Count Days
Is there any VB code to get total days of each 12 months when i put month name
eg:- we knew that , January = 31 days February = 29 Days March = 31 days April =30 days and so on. So if i enter January , i want to get '31' in other column .


Can anybody help me? I know IF formula can do this. But I don't want to copy and paste the very long IF formula . so that i need a VB code.

Count Days
Hi experts,

I am doing a program to plan tasks and i need to count days :
i need to know for example that the 6 june 2004 is the 184th day of the year.
(it s just an example i don't even know if it s true )

The argument will be 6 june 2004 (or a date format like 06-06-04) and the answer should be 184.

How can i do this ?

Thank you

Count The Days
Hi,

Is there a vb function that takes two dates (with format DD,MM,YY) and tell me the number of days in between the two. I normally wouldn't need something like this but with the new active directory tokens you have to supply the number of days to leave a user active, instead of a date.

Thanks!

Count Down Days
Hi All

I have an access database with a field called Days remaining, and what i would like to do is have the field linked to the computers date so that if the field has 365 days, every day that passes it counts down 1 so 364, 363 etc, i have no idea where to start, please can someone help me.

Many thanks

Count Working Days
I have another complex question too. I had been trying so hard, but in vain. I am sure those experts out there can help me.

I am doing a program for staff allocation of my company. We are allocating hours for a staff on a weekly basis, that is, on every saturday of each month. It goes like this.

Eg: The month February has 4 saturdays. The full working hours of a staff is 40 hours.

February

7 14 21 28

But the availability of the staff is calculated in percentage. The calculation is as follows:

Availabality=No. of hours per month assigned to a staff / Total no. of working hours of a month * 100

My confusion is that the hours are assigned on a weekly basis regardless of the month. In the above example, I could not get the working hours because, the days before 7th is in the previous week and also the days after 28th includes the days of the next month.

Can anybody help me please?

Thanks

Count Working Days
Hi all,

Is there a way to get the number of working days of a particular month?
Please help.

Thanks

Count Working Days
Hi,

is there an easy ways to count working days between 2 dates, i.e. not including saturdays and sundays.

cheers
Nick

How To Count The Duration Time Within 2 Days?
hi all,

i just want to ask about how to count the duration time within 2 days?for example from 01/08/2005 to 06/08/2005 manually count as 5 days and 120hours.can the system do it?please help.tq in advance.

Count Total Days For A Year
May i know the code for counting total days for a year?

Thank you.

Count Number Of Days Excluding Sundays
I am going to count the number of days difference of one date from the other, but, excluding Sundays. How can I detect how many sundays are there in between the two dates?

Please help me.

Rif: Count No Of Days Between 2 Dates In 'dd/mm/yyyy' Format
StartDate = CDate(Form.TextBoxStart.Text) or (Form.LabelStart.Caption)

EndDate = CDate(Form.TextBoxEnd.Text) or (Form.LabelEnd.Caption)

Format Start and End Dates "dd/mmm/yyyy" same will become 01 Jan 2003

DaysInterval = DateDiff("d", StartDate, EndDate)

I think your error is due to different operating system settings for dats i
e. :

02/01/2003 can be interpreted as Feb 1 2003 or Jan 2 2003 depending on
regional setting; error seems not arise if day or month two digits are >12

Hope will help

Michele Rossi





-------Messaggio originale-------



Da: visualbasic-l@OpenITx.com

Data: sabato 03 maggio 2003 09.22.33

A: email@removed

Oggetto: [visualbasic-l] Count No of Days between 2 Dates in 'dd/mm/yyyy'
Format



Archive Page - http://www.ittoolbox.com//I/visualbasic.asp



Hi all,

I=A1=A6m encountering the problem in calculating the numer of
days correctly between 2 dates after I convert the date format from
=A1=A5mm/dd/yyyy=A1=A6 to =A1=A5dd/mm/yyyy=A1=A6.

Appreciate your expertise to enlighten me to get the
computation works correctly.



Thanks and regards,

YC



Dim x As Variant

Dim y As Variant

Dim z As Variant

Dim a As Variant

Count No Of Days Between 2 Dates In 'dd/mm/yyyy' Format
<html><div style='background-color:'><DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3>Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3><SPAN style="mso-tab-count: 1">        & nbsp;       </SPAN>I・m encountering the problem in calculating the number of days correctly between 2 dates after I convert the date format from .mm/dd/yyyy・ to .dd/mm/yyyy・.<SPAN style="mso-spacerun: yes">  </SPAN><o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3><SPAN style="mso-tab-count: 1">        & nbsp;       </SPAN>Appreciate your expertise to enlighten me to get the computation works correctly. <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3> <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3>Thanks and regards,<o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3>YC <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt"><FONT size=3> <o:p></o:p></FONT></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt">Dim x As Variant<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt">Dim y As Variant<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt">Dim z As Variant<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt">Dim a As Variant<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><SPAN style="FONT-SIZE: 10pt; mso-bidi-font-size: 12.0pt">

How Can I Count 15 Days For Distribute Trial Version Of My Vb Application.
hi..
how can i count 15 days for distribute trial version of my vb application which
not depend on user system time.

??

Count No. Of Days With Start And End Date In DD/MM/YYYY Format
<html><div style='background-color:'><DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Dear all,</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Thank you very much especially to<SPAN style="mso-spacerun: yes">  </SPAN>Vikrams16, Michael Dsa,<SPAN style="FONT-SIZE: 8.5pt; COLOR: black; FONT-FAMILY: Tahoma"> </SPAN><SPAN style="COLOR: black; mso-bidi-font-size: 8.5pt">Pswalia</SPAN> , Tata and <SPAN style="mso-spacerun: yes"> </SPAN>Chris McDonald<SPAN style="mso-spacerun: yes">  </SPAN><SPAN style="mso-spacerun: yes"> </SPAN>for your prompt assistance.<SPAN style="mso-spacerun: yes">  </SPAN>The problem on excluding Sunday from working day is solved now.<SPAN style="mso-spacerun: yes">  </SPAN>But there is another<SPAN style="mso-spacerun: yes">  </SPAN>problem related to number of days counting for a long period.<SPAN style="mso-spacerun: yes">  </SPAN>For instance, with the start date<SPAN style="mso-spacerun: yes">  </SPAN>1 April 2003 and end date 30 April 2003, the number of days appears as 99 days.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">I suspect it is caused by my date format dd/mm/yyyy.<SPAN style="mso-spacerun: yes">  </SPAN>However, I have already converted it in my code.<SPAN style="mso-spacerun: yes">   </SPAN>For your info, the number of days calculation works perfectly if I use the date format in mm/dd/yyyy.<SPAN style="mso-spacerun: yes">  </SPAN>Could someone there enlighten me by reviewing the following codes.<SPAN style="mso-spacerun: yes">  </SPAN>Many thanks in advance.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">

Date Format, Count No. Of Days & Highlight Date In Calendar
<html><div style='background-color:'><DIV>
<P>Hi all,</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Happen to visit this website accidentally and found that its very useful to VB beginner like me. <SPAN style="mso-spacerun: yes"> </SPAN>I have the following problems and would appreciate your kind assistance to enlighten me.<SPAN style="mso-spacerun: yes">  </SPAN>Attached is my file for your checking.<SPAN style="mso-spacerun: yes">  </SPAN></P></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> <?XML:NAMESPACE PREFIX = O /><O:P></O:P></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt 0.5in; TEXT-INDENT: -0.25in; tab-stops: list .5in; mso-list: l0 level1 lfo3">1)<SPAN style="FONT: 7pt 'Times New Roman'">      </SPAN>Sometimes, the number of days is incorrect with a big figure (e.g from 1 to 30 Apr) or even a negative figure after I change the systems date format from mm/dd/yyyy to dd/mm/yyyy.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt 0.25in"> <O:P></O:P></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt 0.5in; TEXT-INDENT: -0.25in; tab-stops: list .5in; mso-list: l0 level1 lfo3">2)<SPAN style="FONT: 7pt 'Times New Roman'">      </SPAN>In calculating the number of days, I need to exclude Sunday.<SPAN style="mso-spacerun: yes">  </SPAN>By using the WeekDay (Calendar1) = 7 method, I still didnt get the correct result.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> </P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><O:P>      3)  How to highlight a certain date in calendar with different colour to indicate it's a public holiday ? </O:P></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"><O:P></O:P> </P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Many thanks in advance.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> <O:P></O:P></P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Best regards,</P>
<DIV></DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">YC </P>
<DIV></DIV>
<DIV></DIV>
<P><BR><BR></P>
<DIV></DIV>
<DIV></DIV></div><br clear=all><hr>Add photos to your e-mail with <a href="http://g.msn.com/8HMVEN/2746">MSN 8.</a> Get 2 months FREE*.</html>

Access Report GroupOn = Week; How To Set MONDAY As The Beginning Of Week For Reporting.
Newbie here....

I need to create MS Access reports that use Monday as the beginning of the
work week where the data is grouped by week. I haven't found any code or
setting examples for Queries where I can do this. Any ideas?

Thanks in advance!

Find Week Start Date From The Week Number
Does anyone know a way to find the start of the week from a given week number?

For example we are in week 32 this year and the start date is 2/08/04.

I have used the following to find the week number:


Code:
Private Sub getWeekNumber()
'This fantastic bit of code returns the week number

Dim iNumberOfTheWeek As Integer

iNumberOfTheWeek = DatePart("ww", Now())

End Sub

But now I want to find the date range that any given week covers.

Any ideas?

How To Get The Week Of A Particular Month And Then Search For The Date In That Week?
Hey guys, I have a problem here. I am at da moment, trying to code the form where I can calculate the week's Statistics of the booked dates in the file.

All Im tryin to do is the user has access to the months 1 - 12 after he clicks on the combo box. after that he can choose the first second or last week from another combo box after this he will be shown the no: of bookings done in that week.

I cant get it working. I got it to 2 work on the month and week by adding them to da combo. but not the process.

please help

Darkshade

Week Number And Week Ending Date In VB 6
Hi,
I'm trying to get the system's week number and its week ending date using the VB FORMAT function. For the week number is pretty simple and it works fine but its week ending date I can't find the function. It has to be a function for this instead of codes.

For my week number is this:


test1 = format (date, "ww") 'it gives me the current week number (numbers from 1 to 52)


Now from that week number I would like to get the week date ending, eg.

for week 19 its ending date would be 05/10/2003 (Saturday May 10, 2003)


Any ideas?


Thank you

Olivia

This Is Driving Me So Mad , I Have Spent A Week Trying To Get Round It, Linking Excel
I have spent a week trying to get round this problem and it is driving me nuts !!!!!!!!!!!!!!!!!!!!!!!!!!!!
Surely I am not the only person to try this , All I want to do is link an excel spreadsheet into access. I am having a problem with one colum , the cells in this colum are mixed format. Some are numbers , some are general and some are blank. I want to link the sheet to a table so I can do some calculations. When I get the link some of the data is all bollocksed up. I know that access looks at the first 8 rows of a colum to determine the data type in access table. So I tried to put a char in front of every cell before importing to access but again my data is incorrect. If I have a number cell with this data:
379,771,137,373,653,000,000,000,000
it is imported as 3.79771137373653E+26 even if I put a char in front to try and make the field appear as text.
Surely someone knows how to link to excel and have the data the way it is in the sheet.

All I want is to get this data into a table so I can use it

Excel Dates &amp; Working Days
Hi All,

Im using MS Excel, I want to write a piece of code that will find a date after 7 working days, i.e Moday to Friday. For example if a cell A1 contains

13/11/2006

I want cell B1 to contain

22/11/2006

This may seem simple by doing an if, e.g.


VB Code:
Sub getDate()dim currentDate as Datedim futureDate as Datedim addValue, dayValue as Integer currentDate = Range.("A1").ValuedayValue = Weekday(currentDate) If dayValue= 1 then     addValue= 8ElseIf dayValue = 2 Then     addValue= 9ElseIf dayValue = 3 Then     addValue= 10'etc.............End If futureDate = currentDate + addValue End Sub


This works ok until I have a Bank / Public Holidays, which are classed as Non-Working Days. For example, I'm from Ireland and the Bank / Public holidays fall on:

01-Jan
02-Jan
17-Mar
12-Apr
01-May
05-Jun
07-Aug
30-Oct
25-Dec
26-Dec

This is for 2006. So if I have the date as

27-10-2006, which is a Friday, I cant just add 12 to the date making it
07-11-2006, because there is a holiday on the Monday, I must add 13
making it 08-11-2006

I was thinking about putting the Holidays in an array and checking the current date against it, but I have thosands of dates and Excel would probably crash.

My head is getting sore trying to think!

Any help would be much appreciated!

Thanks for reading!
mccreaso

Code Worked Last Week, Now I Get An Error. Excel .ChangeFileAccess(xlreadonly)
I ran this probably 100 times last week now it gives me an error 1004.

Sub MakeReadOnly()
  ActiveWorkbook.ChangeFileAccess (xlReadOnly)
End Sub

I tried the line I found at Microsoft
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
 his line also dose not work. What is going on.

I am using Excel 2002 SP3.



Edited by - beraynor on 1/23/2006 8:18:37 AM

Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function.  The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########".  What did I do wrong?

Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.

Conditonal Format Cells In Excel If Older Than 4 Days
I can anyone help me with a slight niggly problem i am having in Excel Please?

i have the following code:

Code:
Sub DateMinus4()

Dim DateX
DateX4 = Date - 4

Range("E:E").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INT(E1)= DateX4()"
Selection.FormatConditions(1).Interior.ColorIndex = 4

MsgBox (DateX4)

End Sub


which basically is supposed to look at todays date. (Date) and take 4 days from it (Date-4) whiich is defined as a variable value of (DateX4) i can prve this bit works as the msgbox displays the correct date value, howevere what i want is for the code to look at a defined column in this case column E and if there is a date value in it of (DateX4) ie four days old, change the colour of the cell to Green, but i can get the syntax to work or the Date variabel to be recognised, can anyone help with the correct syntax to use a variabel in a statement such as this?

many thanks K.

Date Of First Day Of The Week From A Week Number
Does anyone know of a way to get the date for the first or last (It really doesn't matter) day a week given a week number?
I'm calculating the week number by using:


VB Code:
lcurrentweek = format(Now, "ww")lcurrentweek + 1...lcurrentweek + 9 'Then i'd like to find the first or last day of each of those  


I guess since I'm just trying to get the first dates of the next nine weeks that i could just find the first date of this week and keep adding seven too it?

Thanks
Andy

Finding The Date Of The 2nd Day Of The Week Of Week 44
how can i find the above?

e.g i want to find for weeknumber 44 what is the date of monday

thnks

Second Different Problem {days In Month, Days In Year}
I am trying to make this script that count the number of second between 2 date with 2 time. Now i am stuck with how to get the number of days in a certain month and year. can someone enlighten me thanks

Conversion Of Given Days To Years,months And Days
dear users,
how do i convert the given no. of days into complete no. of years, the remaining no. of months and the remaining no. of days using VB6.

Row Count In Excel
Probably been asked before but racking my brain lately on this one.

Need a VB sub routine to count the number of rows of data in an Excel sheet, Data.

Then output the value of the count in a cell, A1.

Anyone help?

Thanks

Count From DBF To Excel
I am trying the following code and keep getting an error:

CODEDim conn As ADODB.Connection
Dim numofrecscopied As Long
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:;Extended Properties=dBASE 5.0;User ID=;Password="
conn.Open
conn.Execute "SELECT STATE, COUNT(*) AS QUANTITY INTO [Excel 8.0;" & _
"Database=C:TEST.XLS].[Sheet1] FROM " & _
"TEST.DBF", numofrecscopied
conn.Close
Set conn = Nothing
MsgBox numofrecscopied & " records copied to Excel!", vbInformation

Excel Can't Count Pixels
Why do I get an Image Control (same with other controls actually) that is rendered 208 pixels wide (excluding border) when I've set it's Width to 160?

The dimensions of a control are supposed to be in pixels according to the VBA documentation (as far as I can tell).

My computer is set to use Normal fonts (96dpi) and I haven't got any weird accessibility settings.

Excel/ String Count
Hi, Can anyone help me with creating a macro.
I've got a spreadsheet and I want to create a macro to find a specific string such as "MDSE" and count how many times it appears in the spreadhsheet and print it out in one of the cells... I"m not sure how to approach it.. can anyone give suggestiongs.

Thanks!!

Count Function In Excel
Hello. I am trying to write VBA code to count the number of rows in an excel spreadsheet. The number or rows will differ each time I open the spreadsheet, so I can't put the simple function "=COUNT(A1:A1000)". If you could help me with this in any way, I would appreciate it! This is what I have been trying, but it has not been working for me....

Range("A1").Select 'Select A1
Firstrow = ActiveCell.Row * -1 'Firstrow = -1
Selection.End(xlDown).Select 'Move down to bottom row
Lastrow = ActiveCell.Row * -1 'Lastrow = -1000
ActiveCell.Offset(1, 0).Select 'Move down one row
ActiveCell.FormulaR1C1 = "=COUNT(R[Lastrow]C:R[Firstrow]C)"


When I do this, I get an error message saying "application defined or object defined error". I have also tried using...

Range ("A1").Select
Firstrow = ActiveCell.Address
Selection.End(xlDown).Select
Lastrow=ActiveCell.Address
ActiveCell.Offset(1,0).Select
ActiveCell.Formula = "=COUNT(Firstrow:Lastrow)"


When I do this, I just get a 0 for the count of the rows.

Does anyone know what else I can try or what I am doing wrong?

Thanks!!!
ActiveCell.Formula = "=COUNT(Lastrow:Firstrow)"

Excel Printout Count
I have a excel file that i print out using vba, the file has 3 sheets with 2 pages on each sheet, but some files i have i don't know excaly how many pages will be printed, is there a way to know how many pages will be printed in a workbook?

How Can I Count Hits To Excel?
Does anyone know how to track how many hits I've had to my Excel application? It's based on a shared server. Can anyone help?

Excel Sheet Count
I'm printing excel sheets individually in a macro. This macro has worked fine for most excel workbooks that I've come across. However, I have one now that is giving me problems.
There are only 2 sheets, none hidden, but when I run through the script and check the Sheets.Count it comes out to 7. There are 6 modules or macros in the workbook, but I disable them when I open the workbook. Would these be counted as additional sheets for some reason in the Sheets.Count?

Excel Reference Count
I am connecting to excel using COM. If excel is running already, I am getting an object reference via GetObject() else starting it with CreateObject().

When I am done, I want to stop excel but only if I started it (If the user had excel open already, I don't want to close it). Is there a reference count/method that will give me the count of processes accessing excel?

The object browser doesn't list anything useful when I search for reference/open/user.

Thanks in advance.

Excel Column Count
I have one excel file with 28 columns.i want to get the column count of that file thru a recordset from vb?

Copyright 2005-08 www.BigResource.com, All rights reserved