Check if weekend exist in date range

[Tharabai]

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]

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]

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]

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]

=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]

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]

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]

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]

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]

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]

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]

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]

@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]

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]

I also prefer the low testosteron/adrenalin driven fora, especially the German ones like e.g. Clever Excel Forum.

[Bob Phillips]

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]