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

Date Of Nearest Monday

Our financial year begins on the Monday nearest the 1st of July. For example the current financial year started on Monday 30/06/2003 which is week 1. I have written a function that takes a date parameter and returns the current financial week. It is not working properly because I assumed that the financial year always starts on the 1st of July. This causes problems because when I subtract the number of days and then divide by 7 to get the week number I sometimes get week 53 because I did not subtract the correct start date of the financial year.

This is my function:

Public Function NextWeek(thedate As Date) As Integer

On Error GoTo errNextWeek:

Dim dtedate As Date, _
strYearStartDate As String, _
intDaysInYear As Integer, _
intCurrentYear As Integer, _
intCurrentWeek As Integer

'get current year
intCurrentYear = Year(thedate)

'first day of financial year
strYearStartDate = "01/07/" & CStr(intCurrentYear)
'need function here to calculate monday closest to 01/07

'get days gone by since beginning of year
intDaysInYear = DateDiff("d", strYearStartDate, thedate, vbMonday)

If intDaysInYear < 0 Then

strYearStartDate = "01/07/" & CStr(intCurrentYear - 1)

'get days gone by since beginning of year
intDaysInYear = DateDiff("d", strYearStartDate, thedate, vbMonday)

End If

'get week number and drop decimal
intCurrentWeek = Round((intDaysInYear / 7), 0)

'return value we need to start at 1 not 0
NextWeek = intCurrentWeek + 1

Exit Function
MsgBox "The following error occurred in NextWeek: " & Err.Description & _
" : " & Err.Number, vbCritical
End Function

I really need help with this one...

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
*RESOLVED* Given A Date Return The Date Of Monday The Same Week
I'm stumped. The title says it all.

I need the date of Monday for the same week as any date entered. I'm using the system settings for the first day of week.

For some reason I can get it to work unless the day selected is the first day of the week. In that case it always selects the monday of the previous week. For all other dates it works fine.

VB Code:
dMonday = DateAdd("d", -Weekday(dSelectedDate, vbMonday) + 1, dSelectedDate)

Monday Date
Is there any way to make sure I can get Monday's date no matter which day of the week I run the report? The purpose is that no matter which day I am working on the report, I want to show a Monday's date on it.

Thank you.

Find Date Of Monday Just Past
Hi All

I found a few threads here that were slightly similar but didn't properly understand them, so any help appreciated.

I need to find the date of the monday of the current week, so if its any day this week (including sunday 19th or monday13th) the returned date is 13/08/07

thanks all

Day Monday
how say the monday for next week example:

if day es tuesday how say the next monday the date

EX: tuesday 29/8/00 the next monday is 4/9/00

else is thursday or friday or saturday or sunday ever say the next monday of next week

sorry my bad english


Monday ?
hi i know this is probaly realy simple but i cant get it to work

i want to detect if it monday is there a way of doing this ?


A Puzzle For A Not-So-Serious Monday
Here, s coding puzzle:

You have 1,000 doors.
You have 1,000 people
The first person is instructed to open all the doors.
The second person is instructed to close every other door.

The third person is to close every third door, if itís open, or open it if itís closed.
This repeats until all 1,000 people do their task.

Question: How many doors are now open?

This is the code I came with to solve the problem. I used Excel to avoid array hassles)
Sub Setup()

For x = 1 To 1000
Cells(x, 1) = "Open"
Next x

End Sub

Sub OpenAndClose()

For y = 2 To 1000
For z = y To 1000 Step y
If Cells(z, 1) = "Open" Then
Cells(z, 1) = "Close"
Cells(z, 1) = "Open"
End If

End Sub

The answer is 31 open doors.

Anyone come up withsomething different?

Dilema. I Need Help Before Monday
Thanks for your time in checking into this.

Here's the problem.

I'm using Access 97 at work to develop a database. I'm just learning how to use Access. I'm familiar with Visual Basic and I'm using a lot of VBA. I need a simple solution for this in accurate detail. No one has been able to help me thus far.

1 - I have a query that a form runs.
2 - The query returns either nothing or 1 record from a table.
3 - It can only be one record, because it's a joined primary key.
4 - This query is done so I know whether or not the record exists.
5 - If (IF THEN) the record does exist I need to update it.
6 - If (ELSE) the record doesn't exist I need to create it.

How do I evalute the values in a Query through VBA code?
Something simple please.

Happy Monday!
Im trunning to run a large amount of queries on an adodb database object. I start with a begin transaction call, and then begin to do my work - selects and updates.

Finally when I get to a delete statement I get an error - Cannot create new connection because in manual or distributed transaction mode.

Can I not use delete statements with a begin transaction/rollback/commit transaction set?

Detect If Its Monday
hi i know this is probaly realy simple but i cant get it to work

i want to detect if it monday is there a way of doing this ?


Week Start Monday
I'm doing a reporting tool for my program and it has some option buttons to select the time scale of the report. One of these is week. At the moment I just subtract 7 days from the current date.

But i want it to return the date of last monday. So anyday during the week it will select the monday of that week, which will give a report from that monday to todays date. Any ideas??

Determining If It's Monday, Friday, Etc.
I'm making a program that alerts me on a certain time. How do Im determine if it's Monday or Friday or any-day?

Thanks in advance

Schedule, Every Second Monday Of The Month, Need Help.
I am trying to create a program that will allow me to select a time frame like the title. I want to preschedule a year of "every second monday of the month" or 3rd or 1st or heck maybe a wednsday. I have tried and tried and searched for controls and writeups and you name it but i still cant find an answer. If someoen could help me out point me in the right direction maybe explain a little on how i could do this i would highly appreciate it! Im so lost!

Also if there is a good control to monitor this let me explain real quick what im doing on this subject.

I need to visit clients houses once a month for routine checkups. My base form will have a calander that "should" highlight all the days that have scheduled checkups on them. When i click them a list on the side would populate the times and clients that need to be visited. It will also have an alert system alerting me of checkups coming up, one for a week worth of work, one for today.

Now because dates might need to change it will have to delete the schedule for that client and from today and here on out reset the schedule to the new time frame ("evey 3rd thursday of the month").

That is a general idea of what the schedule would due but i just cant seem to get it to do well anything like that and even after searching for few hours i cant even think of where to begin!

Any help would be greatly appreciated, know any great tutorials, or controls, anything really ive been all over and i cant find anything there either.

Thanks for reading!

in the begining man created code.
in the end code will create man.
clones are coming only matter of time.

How To Get The System Day Ie Monday In A Textbox
Hi, I have a user form with a textbox and I need to have the current day shown in the textbox when the form opens (UserForm_Initialize())

Any advise would be appreciated, thanks


Monday Morning Message Box Blues
I can tell it's Monday... nothing is working

I have been fidling around trying to get a message box to work and it just doesn't want to play ball. I have tried laying them out as instructed in VBA help; also as shown in my VBA book and have tried using example code online but nothing seems to work.

From help:

Msg = "The name: " & strBusSec " does not already exist and so maybe added to the tree list on Agresso."
Style = vbOKOnly
Title = "Available Agresso Name"
Response = MsgBox(Msg, Style, Title)
If Response = "OK" Then
End Sub
End If

From code snipets on this forum:

MsgBox prompt:="The name: " & strBusSec " does not already exist and so maybe added to the tree list on Agresso.", _
Exit Sub

I think it is because i am trying to have a variable name appear halfway through the message text, but i can't find and example of any code that shows this. Is it just not possible, or am i just being Dim.

Cheers in advance for any help,


It's A Monday And I'm Having Brain Farts: Quick Question
Gotta love mondays..

I have this class module and It has a function in it. I need to somehow link this module with my form coding so I don't have to clutter up my form coding. I also want to use this function in the class module throughout all of my forms.. I was thinking that I have to go like so:

Dim SomeWordHere As New MyClassModule

But it didn't work.. Could someone please help me?

Thanks in advance


Start My Program Every Monday Morning At 3 A Clock?
My program should start every Monday morning at 3 a clock. How do I do that?

Find Monday Giving Year/week
Hi to everyone.

I have to show the date of the Monday of a given week of the year:

FindMonday(30, 2003) = "21/07/03".

Does anybody suggest me how should by the function "FindMonday" made?

Thank you for the answer.

Round To The Nearest .25
I have a program that divides up values (like 2.5, 7, 5.25, etc..) and I need it to round to the nearest quarter of a whole (round to 1.25 or 1.5 or 1.75)

is there an easy way to do this?

Rounding To The Nearest .5%

Does anyone know how to round to half a percent?


57.48 = 57.5%
56.43 = 56.5%
55.90 = 56%
51.70 = 51.5% (edited)

I did a search on rounding but only got to go up/down to a whole number



Round Up To Nearest
i am dealing with currency variables and what i want to do is always round up to the nearest 10p.

if it was 33.42 then i want it to return 33.50 but if its already on a full 10p like 33.40 then do nothing.


Rounding Off To The Nearest 5 In VB6
I have a currency amount that I always want to round off the the nearest 5. I would like the currency amount to round off downwards to the nearest five.

Eg. $459 = $455
Eg. $456 = $455
Eg. $451 = $450
Eg. $454 = $450

ie. going down to the nearest 5.

I am not at all experienced in VB6 so any help will be appreciated.

How To Make A Value To Nearest Ten (e.g. 47 To 50 Or 43 To 40)
How to make a value to nearest ten (e.g. 47 to 50 or 43 to 40)

Please help me for VB6.

Round To Nearest 0.25
I saw rounding to the nearest 5 here. I tried that method to round to the nearest 0.25, but it gave me a divide by zero error. Is there any other way to round to the nearest 0.25? Thanks.

Round To Nearest 5.
How would I round a number down to the nearest 5? ie 7 becomes 5

Rounding To The Nearest Whole Five
Hi Everyone,
I am wondering if anyone would know how to round a number to the nearest whole 5. I saw an example code online somewhere of being able to do it using the round() function but I cant find it.
Thanks everyone for the help!

Rounding To Nearest 25,000
This is not an ordinary rounding issue. Here's what I need to do. I have to round any number up to the nearest 25,000 increment.

For example:

124,698 would need to round to 125,000
126,125 would need to round to 150,000
1,278,189 would need to round to 1,300,000

Anyone know of a mathematical formula for doing this? Or any good ideas as to how to accomplish this? THANKS!

Nearest Integer
Is there a built-in function in VB6 for calculating the "nearest integer" of a non-integer number? For example,

Nearest Integer(2.4) = 2
Nearest Integer(2.5) = 3
Nearest Integer(2.8) = 3

Rounding To Nearest .25
I'm looking for a way to automatically round numbers and times to the nearest .25.
Right now I'm simply checking the numbers. e.g. if the decimal portion <> 00 or .25 or .50 or .75 then show a message box telling the user to round the input. Likewise, minutes are checked for 00 or 15 or 30 or 45.
Any ideas will be greatly appreciated.

Nearest Friday&#039;s
Hi there

Want to know how do I return a date of the nearest friday. At the minute if I enter a start date and number of weeks it returns a date exactly that number of weeks later. That is if I enter 23/08/2000 and then 1 week, i get 30/08/2000 back which is a wednesday(I think!!) what I want returned is fridays date 01/09/2000. How do I do this?

This is my function at the minute

Public Function CalcPeriodEnd(StartDate As Variant, NoWeeks As Variant) As Variant
On Error GoTo Proc_Err
Dim NewDate As Variant

CalcPeriodEnd = 0
NewDate = DateAdd("ww", NoWeeks, StartDate)

CalcPeriodEnd = Format$(NewDate, "dd/mm/yyyy")

'just to check output
'MsgBox Format$(NewDate, "dd/mm/yyyy"), vbOKOnly

Exit Function
End Function

Thanks in advance

Find Nearest AP

I got a new project on WIFI technology. I am facing a problem. The problem is when user is accessing my desktop application through internet. I have to identify which Access point he is using. In other words I want to find out the current physical location of the member programmatically. Is there any way to find out?


Round Down To Nearest 5
Hi all,

I have a calculation that will return an integer between 1 and 120.

I want to be able to round the result down to the nearest 5.

ie if result = 78 then round down to 75
if result = 15 then stick at 15

any ideas anyone

many thanks

How Do You Round To The Nearest Tenth?
How do you round to the nearest tenth?

Rounding To Nearest Eighth
Is there a function for rounding to the nearest increment (I need 0.125 in my case)? For example, Excel has a function called MROUND. Does VB have anything like this? I would like to stay away from iterative loops if possible.

I've searched and can only find information on general rounding. I have read many useful posts and saw an interesting thread regarding rounding to the nearest tens place. However, nothing seems relevant to my problem.

Thanks in advance!

Finding The Nearest Server
Good Morning. Is there a way to find the name of the nearest NT server that a drive can be mapped to? Or at least the one that the current user is logged into?


Reallocate To Nearest PowerOfTwo

I'm checking if the dimension of an one dimenisonal array is a power of two with a function that i found in wikipedia. If the array is not a power of two then how can i fill the array atomatically with zeros to the nearest power of two??? For example if the array is (0 To 12) then it should become (0 To 15) filling the array with three zeros(at the end). Any ideas about a function like that????


Rounding Down To Nearest Nickel
Is there a vb function that would allow me to round down to the nearest nickel or should I just write something that will change it as a string?

Rouding To Nearest Number
Hello All

I am usin WMI to return the amount of memory on a system, but it is returning a value such as...

1023.997 Mb

I want it to display 1024?

How is this done?

Thank you

Round To Nearest Hundred...
okie dokie, i just need the simplest code/ function / anything to round to the nearest hundred. like if i have 1246 i want 1200 if i have 1256 i want 1300, no big deal, need it for school, but not neccasarrily just those two numbers yaknow ?

Nearest Quarter Hour

In the code below, LaborHours is a text box on the form.
Is there a better way to do this?

If LaborHours - Int(LaborHours) <> Abs(0) And _
LaborHours - Int(LaborHours) <> Abs(0.25) And _
LaborHours - Int(LaborHours) <> Abs(0.5) And _
LaborHours - Int(LaborHours) <> Abs(0.75) Then
MsgBox ("Labor Hours must be to the nearest 1/4 hour.")
End If


Nearest Numbers/multiples
How would I go about doing this:

First, you would need a line. The arrow keys move the X2 and Y2 properties around the form. I want them to move by increments of ten. When I press U, They both move to the nearest multiple of 120. So if X was at 1210 and Y were at 1275, X would equal 1200 and Y would equal 1320.

Rounding To Nearest Quarter
Someone posted a thread looking for a way to round a number to the nearest quarter (.25).
This thread must have been deleted before I had a chance to respond. Here is how you do it:

If (mynumber / 0.25) * 100 Mod 100 Then 'checks to see if number really needs rounding
'UpDown = 0.5 rounds up or down to nearest quarter
'UpDown = 0 rounds down to nearest quarter
'UpDown = 1 rounds up to nearest quarter
UpDown = 0.5
mynumber = Format((Int(mynumber * 4 + UpDown)) / 4, "$0.00")
mynumber = Format(mynumber, "$0.00") 'number did not need rounding
End If

Harry W posted a solution as I remember, but it did not work correctly:


'This code will Round down:
dblValue = (Int(dblValue * 4 + 1)) / 4

'This code will round up:
dblValue = (Int(dblValue * 4 + 2)) / 4

'This code will round to the nearest 0.25:
dblValue = (Int(dblValue * 4 + 1.5)) / 4

You can try this little example out:

Private Sub Form_Load()
Dim mynumber As String
Dim arr
arr = Array(1.73, 1.25, 0.03, 0.15, 0.37, 0.92, 1.75, 1.5, 0.62, 2.38, 1200.23)
For x = 0 To UBound(arr)
mynumber = Format(arr(x), "0.00")
'Round up to a quarter
If (mynumber / 0.25) * 100 Mod 100 Then 'checks to see if number really needs rounding
'UpDown = 0.5 rounds up or down to nearest quarter
'UpDown = 0 rounds down to nearest quarter
'UpDown = 1 rounds up to nearest quarter
UpDown = 0.5
Debug.Print mynumber; " "; Format((Int(mynumber * 4 + UpDown)) / 4, "$0.00")
Debug.Print mynumber; " "; Format(mynumber, "0.00") 'number did not need rounding
End If

Next x
arr = Array(vbNull)

End Sub

[Edited by dsy5 on 10-15-2000 at 03:30 PM]

Rounding Up To Nearest 50 Cents
Is there a simple way of rounding UP to the nearest .50 cents

12.00  =  12.00
12.01  =  12.50
12.49  =  12.50

12.50  =  12.50
12.51  =  13.00
12.78  =  13.00

I can do it by writing a lot of ifs' etc.
Was interested if there is a simple way of doing this.

Appreciate any help

How Do I Find The Nearest Number?
I have another problem with Access, i want to find a row based on the closest number i.e.

these are my numbers, they are set but there is no set pattern to them:


my problem is , if someone puts in 150 in the [Page Count] field, i want it to put 145 in the [Counter] field because this is the closest number

I used to use INDEX() and MATCH() on Excel but of course this does not work with Access


Round Down To The Nearest Smallest Possible Multiply
VB Code:
Public Function rd(number as long, multiply as long) as longIf number = 1 Thenrd = 1Elserd = Int((number - 1) / multiply) * multiply + 1End If End Function

This is what it will return

rd(1, 9) = 1
rd(2, 9) = 1
rd(3, 9) = 1
rd(4, 9) = 1
rd(9, 9) = 1
rd(10, 9) = 10
rd(15, 9) = 10
rd(18, 9) = 10
rd(19, 9) = 19
rd(20, 9) = 19

it rounds down to the nearest smallest multiply of 9 and add 1 to the result.

i already got the function working, but i was wondering whether there is a faster way of doing it, since the function is going to be called alot

thanks in advance

VB Function To Round To The Nearest Integer

can you guys tell me what is the VB functio nto round to the nearest integer.

the equivalent of c's ceil?

Rounding Currency To Nearest 5cents

I have to round a currency total to the nearest 5 cents. Is there an inbuilt function to do it better?

VB Code:
Dim temp As Currency   temp = TotalCost * 10 - Int(TotalCost * 10)   If temp < 0.5 Then     TotalCost = TotalCost - temp/10   ElseIf temp > 0.5 Then      TotalCost = Round(TotalCost + 0.05, 1)   End If


**RESOLVED** Rounding To The Nearest Whole Number

I need help! I have conducted a search on this forum and have
found nothing.... what I need help with is rounding to the nearest
whole number ???

1.5 becomes 2.0
1.4 becomes 1.0

etc etc

Your help and time is very much appreciated

Round Time To The Nearest 15 Minutes?
Does anyone have a lil function / know how to round time to the nearest 15 minutes?

I have broken the day up into 15 minute intervals, and I would like to be able to default to the current interval.

08:40 = 08:30
06:16 = 06:15

Does this make sense?

Thanks alot for your help.

Copyright © 2005-08, All rights reserved