Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
Hi,
Is this possible to track dates of a month except Sunday by running a macro. Akash |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
Dear Sir,
I want that as soon as the User presses a shortcut key in Excel then the system should prompt for the Month & the year and as soon as the user enters January 2007 then the system should generate track dates of a month except Sunday. Is it possible. Pls do help me in this regards Akash Maheshwari On Dec 30, 1:29 pm, "Akash" wrote: Hi, Is this possible to track dates of a month except Sunday by running a macro. Akash |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
Yes it is try the follwoing code:
[B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0) [B2]=B1+IF(WEEKDAY(B1,1)=7,2,1) and then copy b2 down for the year. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Akash" wrote: Hi, Is this possible to track dates of a month except Sunday by running a macro. Akash |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
Hi Martin,
Thanks for the wonderful solution. Its working very fine. Moreover if this can be done through Macro it would be very benificial for us. I want that as soon as the User presses a shortcut key in Excel then the system should prompt for the Month & the year and as soon as the user enters January 2007 then the system should generate track dates of a month except Sunday. Is it possible. Pls do help me in this regards Akash Maheshwari On Dec 30, 2:09 pm, Martin Fishlock wrote: Yes it is try the follwoing code: [B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0) [B2]=B1+IF(WEEKDAY(B1,1)=7,2,1) and then copy b2 down for the year. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Akash" wrote: Hi, Is this possible to track dates of a month except Sunday by running a macro. Akash |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO TRACK DATES OF A MONTH EXCEPT SUNDAY
This should work as a starter, you may need to modify the input and you could
use a userform if you so desired.: Option Explicit Sub filldate() Dim s As String Dim d As Date Dim m As Integer Dim y As Integer Dim r As Long ' row offset On Error Resume Next r = 0 Do s = InputBox("Enter the year", _ "Working days ...", Year(Now())) If s = "" Then Exit Sub y = s Loop Until y 2000 And y < Year(Now()) + 10 Do s = InputBox("Enter the month [1.12]", _ "Working days ...", Month(Now())) If s = "" Then Exit Sub m = s Loop Until m 0 And m < 13 d = DateSerial(y, m, 1) With ActiveSheet.Range("A2") Do While Month(d) = m If Weekday(d, vbSunday) < vbSunday Then .Offset(r, 0) = d .Offset(r, 0).NumberFormat = _ "dddd, dd.mm.yyyy" r = r + 1 End If d = d + 1 Loop End With End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Akash" wrote: Hi Martin, Thanks for the wonderful solution. Its working very fine. Moreover if this can be done through Macro it would be very benificial for us. I want that as soon as the User presses a shortcut key in Excel then the system should prompt for the Month & the year and as soon as the user enters January 2007 then the system should generate track dates of a month except Sunday. Is it possible. Pls do help me in this regards Akash Maheshwari On Dec 30, 2:09 pm, Martin Fishlock wrote: Yes it is try the follwoing code: [B1]=DATE(2007,1,1)+IF(WEEKDAY(DATE(2007,1,1))=7,1,0) [B2]=B1+IF(WEEKDAY(B1,1)=7,2,1) and then copy b2 down for the year. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Akash" wrote: Hi, Is this possible to track dates of a month except Sunday by running a macro. Akash |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to exclude dates which equal SUNDAY from a fill series? | Excel Worksheet Functions | |||
Finding 3rd (or 2nd) Sunday in a given year/month | Excel Worksheet Functions | |||
Timesheet Calculate Sunday times that changes every month | Excel Discussion (Misc queries) | |||
Tacking All Sunday & Saturday in a Year along with the dates | Excel Programming | |||
Date Command to Get the Sunday Before the First of the Month | Excel Worksheet Functions |