PDA

View Full Version : Check if weekend exist in date range



Tharabai
08-23-2015, 09:54 AM
Hi,I have an excel sheet dates "From date" in column R and "To date" in column S. I want to know if there are week ends in between the date range.In some of the rows only the From date is available and To date may be blank.Can this be done using macro or if formula pls let me know how to apply in the vba code..- Tharabai

SamT
08-23-2015, 12:00 PM
Use this Formula in Row 2 of the column you want the results in. You can drag it down as many cells as needed.

=HasWeekends($R2,$S2)

and put this code in a Regular module.

Public Function HasWeekEnds(FromDate As Range, ToDate As Range) As Boolean
Dim Ds As Long
Dim D As Long

'Check various Cell formats
If FromDate = "" Or ToDate = "" Then Exit Function
If FromDate = 0 Or ToDate = 0 Then Exit Function

Ds = DateDiff("d", FromDate, ToDate)

For D = 0 To Ds
If Weekday(FormDate.Value + D) = vbSunday _
Or Weekday(FromDate.Value + D) = vbSaturday Then
HasWeekEnds = True
Exit Function
End If
Next D

End Function

mikerickson
08-23-2015, 01:19 PM
If you have start date in A1 and end date in B1, this will return True/False "is there a Sat or Sun between the two dates, inclusive"

=(B1-A1)>=(6-WEEKDAY(A1,2))

Tharabai
08-24-2015, 06:06 AM
Thank you so much for the coding..

Can this function return the date of weekends instead on TRUE or FALSE.
Also, my macro takes verylong time to run for the file which contains about 1lakh rows.

Is there a way to speed up the results.

-Tharabai

mikerickson
08-24-2015, 06:35 AM
=A1+(6-WEEKDAY(A1,2))
Will return the date that the next weekend begins.

I.E if its is friday, that will return tommow's date
if it is Saturday, that will return the same date
If it is Sunday, it will return yesterday's date
If it is Monday, it will return the date 5 days from then.

G6SGA
03-24-2022, 12:52 AM
Use this Formula in Row 2 of the column you want the results in. You can drag it down as many cells as needed.

=HasWeekends($R2,$S2)

and put this code in a Regular module.

Public Function HasWeekEnds(FromDate As Range, ToDate As Range) As Boolean
Dim Ds As Long
Dim D As Long

'Check various Cell formats
If FromDate = "" Or ToDate = "" Then Exit Function
If FromDate = 0 Or ToDate = 0 Then Exit Function

Ds = DateDiff("d", FromDate, ToDate)

For D = 0 To Ds
If Weekday(FormDate.Value + D) = vbSunday _
Or Weekday(FromDate.Value + D) = vbSaturday Then
HasWeekEnds = True
Exit Function
End If
Next D

End Function

Aussiebear
03-24-2022, 01:09 AM
What appears to be the actual issue G6SGA? Are you complaining about the "errrors I leeve in" section. That is simply SamT's satirical signature to see if people actually read the post.

G6SGA
03-24-2022, 02:39 AM
Hi, and thanks for the pick up.
I too was being a little satirical...
With ERROS make you (yoda) [My deliberate error]
Form & From is a miss spelling / dyslexia / auto correct; whatever, in the code itself. And I was noting his homework requirements.


Stephen
G6SGA

Aussiebear
03-24-2022, 02:47 AM
Well Stephen, a non sensical post doesn't do anyone any great value. Perhaps you might like to apologise to SamT, given that he's a respected member of this community, whilst you are for all intents a wanker.

StephenT
03-24-2022, 04:59 AM
Hello, I am sorry if I offend anybody, including you Aussie bear that was not my intent.

I was, wrongly obviously, trying to be humorous.

Which for my first posts here did not go down too well.

SamT -- Sorry again and thanks for the code which I am playing with and today is obviously a learning curve.

P.S. If you wish to bar this email address too that is fine.


Stephen

georgiboy
03-24-2022, 07:52 AM
I am a self-proclaimed "not a very nice person" SoOOoo I tend to hover over the 'Post Quick Reply' button and think to myself "Is that diplomatic? - Could it offend?"

This particular forum has a select number of close "Oldies ;)" along with moderators that have been here a very long time and in my experience you get away with less here than you will elsewhere. Not that you should behave negatively anywhere.

Bob Phillips
03-24-2022, 11:45 AM
I am a self-proclaimed "not a very nice person" SoOOoo I tend to hover over the 'Post Quick Reply' button and think to myself "Is that diplomatic? - Could it offend?"

This particular forum has a select number of close "Oldies ;)" along with moderators that have been here a very long time and in my experience you get away with less here than you will elsewhere. Not that you should behave negatively anywhere.

Interesting Georgi, I would have said the exact opposite. MrExcel is very cliquey, and if you aren't one of the gang, lo and behold. Excel Forum is over-moderated, sneeze and you're out. Excel Guru has many of the same moderators as Excel Forum, but we keep them a bit calmer. Here is the best of all IMO. Stephen post wasn't very useful I agree, but once he explained he thought he was being funny, he was crass at worst (again, IMO). Nothing to apologise for , certainly not to strike him off for, hopefully we will see a more positive contribution in future.

Paul_Hossler
03-24-2022, 02:24 PM
@StephenT -- Well, today we learned that sometimes humor (or humour) doesn't travel too well.

You explanation as to intent is accepted and we look forward to more meaningful contributions in the future



PS - I'm glad there's a delete key on my phone since many times I'll look at a text and decide that it's not as funny as I thought

georgiboy
03-24-2022, 11:59 PM
Giving my post some thought (after reading Bob's post) I retract the below:

in my experience you get away with less here than you will elsewhere.

On the basis of:
I am banned from Mr Excel
I am banned from Excel Forum: Have a new account (with a strike)

This is a good forum with nice people - I have been here since my journey of learning VBA started.

snb
03-25-2022, 01:37 AM
I also prefer the low testosteron/adrenalin driven fora, especially the German ones like e.g. Clever Excel Forum.

Bob Phillips
03-25-2022, 05:00 AM
Giving my post some thought (after reading Bob's post) I retract the below:

On the basis of:
I am banned from Mr Excel
I am banned from Excel Forum: Have a new account (with a strike)

LOL! I am also banned from MrExcel, and I previously received a strike at Excel Forum, but I think it has lapsed now.

SamT
03-25-2022, 11:24 AM
:hi: :yes :rofl:

:devil2: