1

Is there a formula tha I can place in Cell L3 that results in a date by comparing the date in cell E7, adding 53 total days to that date, excluding any Sundays within that period. The resulting date would be e. g.. (E7) 3/4/2014 plus 53 days not counting any Sundays in its way and givcing me that forward date in cell L3.

I would really appreciate any help on this please :-)

  • @Bigbio2002 But won't there sometimes be a different number of Sundays included within the range? For example, if your initial date is on a Monday, it will have one less Sunday included compared to if it were a Saturday. – bdr9 Apr 24 '14 at 14:46
  • It's a work in progress... This is a tough problem. – Bigbio2002 Apr 24 '14 at 14:48
  • Just to confirm - you want to add MORE than 53 actual days because some of them are Sundays? Or add LESS than 53 actual days because some of them are Sundays? – Shevek Apr 24 '14 at 15:08
  • @Shevek It is correct that I actually want to add "more" than 53 days because some of them are Sundays – Rebecca Rickey Apr 24 '14 at 16:51
  • @Bigbio2002 There will a different number of Sundays based on the date that is provided so I need something that can actually see the current calendar and account for them – Rebecca Rickey Apr 24 '14 at 16:52
2

The following formula can be adapted for any number of days (53 could be replaced with another number or a cell reference):

=E7+53+ROUNDDOWN(53/7,0)+IF((MOD(53,7)+WEEKDAY(E7,2))>6,1,0)
|improve this answer|||||
  • Do you mean ROUNDDOWN(53/7,0) instead of ROUNDDOWN(E7/7,0)? – bdr9 Apr 24 '14 at 16:45
  • Yes, thanks, fixed. I had the components of this as cell references, and substituted incorrectly. – Madball73 Apr 24 '14 at 16:58
  • @bdr9 Thankyou so much for all your help between you and Madball73 it works perfectly! – Rebecca Rickey Apr 24 '14 at 18:16
  • @madball73 Thankyou so much for all your help between you and bdr9 it works perfectly! – Rebecca Rickey Apr 24 '14 at 18:18
1

The following formula will add 53 days not including Sundays to the date in cell E7.

=E7+60+IF(WEEKDAY(E7)>=4, 1, 0)

There will always be at least 60 days added, because there are 53 to start with, plus at least 7 Sundays. Then, if the original date was a Wednesday, Thursday, Friday or Saturday, one additional day must be added because one more Sunday is included.

|improve this answer|||||
  • This will only work for 53. What happens if you want to add any other number of days? – Shevek Apr 24 '14 at 15:04
  • @Shevek Then you would have to use a different formula. I just addressed the original question. – bdr9 Apr 24 '14 at 16:46
  • @Shevek Thank you so much for your help. I believe the formula below covers my needs but again thank you so much for offering. – Rebecca Rickey Apr 24 '14 at 18:17
1

You can do this easily with a VBA macro:

Function CalcDate(startDate, increment) As Date

    endDate = DateAdd("d", increment, Date)

    sundays = 0

    For i = startDate To endDate
        If Weekday(i) = vbSunday Then
            sundays = sundays + 1
        End If
    Next

    If sundays > 0 Then
        endDate = DateAdd("d", sundays, endDate)
    End If

    CalcDate = endDate

End Function

You can then call this as you would any other built in Function:

=CalcDate(E7,53)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.