+ Reply to Thread
Results 1 to 4 of 4

Find the 5th for each month from a row of week commencing dates...

  1. #1
    Registered User
    Join Date
    07-17-2010
    Location
    Sheffield
    MS-Off Ver
    Excel 2003
    Posts
    2

    Find the 5th for each month from a row of week commencing dates...

    Hi everyone,

    newbie here so please be gentle!

    I'm trying to create a formula which will be used in a cash forecast spreadsheet.

    Basically, i have a list of payments down column A in cells A2:A11, and across the top row starting from cell B1 is the date of the first day of that week

    I have successfully created a formula in the Tax row cells so that 30 is shown if the week beginning on the above date contains the last working day of the month, i did this by using:


    =IF(AND(DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))


    However, rent is always paid on the 5th of every month (as opposed the the last working day of the month as in the above formula), except when this falls on a weekend, in which case it is deducted on the nearest working day before the 5th.

    How can i change the formula to reflect this?

    I tried:

    =IF(AND(DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))

    which nearly worked apart from in September, where the the nearest working day to the 5th is the 3rd. This date falls in the week commencing 30.08.10, however the formula calculates that the date rent would be paid in that month is the 5th of August, which is correct i suppose, but it doesn't calculate that the 5th of the following month (September) actually falls in this week - then the next week on my sheet starts w/c 06.09.10 which is obviously after the 03.09.10 and so doesn't show any rent payments either, in effect 'skipping' this payment.

    Is there a way for excel to use the w/c date on my sheet, and calculate that the 5th of September falls in the week commending the 30.08.10 and so input 30?

    I don't think i'm a million miles away I just think i need some help in fine tuning, but if anyone has a better formula i'd be eternally grateful!

    Thanks in advance,

    Andy!

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find the 5th for each month from a row of week commencing dates...

    Hello Andy, try this formula

    =IF(AND(DAY(B1+6)>4,DAY(B1+6)<12),30,"")

    ....and for last working day try

    =IF(MONTH(B1+7)<>MONTH(B1),30,"")
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-17-2010
    Location
    Sheffield
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Find the 5th for each month from a row of week commencing dates...

    Quote Originally Posted by daddylonglegs View Post
    Hello Andy, try this formula

    =IF(AND(DAY(B1+6)>4,DAY(B1+6)<12),30,"")

    ....and for last working day try

    =IF(MONTH(B1+7)<>MONTH(B1),30,"")
    Thats brilliant mate! - Works a treat!!

    One question...HOW???

    I know it sound silly to query something when it works, but i would love to know how, as i may have to use this again, only using different recurring dates for every month (e.g. Rent on the 5th, rates on the 12th, and lease/rental charges on the 23rd)

    How does this formula work, or how can it be adapted to use for differing dates? I also don't get (but am extremeley grateful!) how it works without using the NETWORKDAYS function? - how does it know to pay on the nearest working day?

    Sorry if this sounds totally dumb, but it may do as i'm pretty much a dummy with excel!!

    Thanks a million for your help!!

    Andy

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find the 5th for each month from a row of week commencing dates...

    Hello Andy,

    The formulas rely on the week start being a Monday. B1+6 gives you the last day of the week so if that is anywhere from the 5th to the 11th inclusive the 5th will be in that week. In the case where the 5th is at the weekend the last date of that week must be the 5th or the 6th so that would also be captured by the same criteria.

    For a generic formula, you can do something similar but not exactly the same. In the case of the 5th (and, of course, 1st to 6th) the scenario is complicated by the fact that the week could start in the previous month so if the day you want is in A2 (e.g. 5) you could use a formula like this to cover all bases

    =IF($A2< 8,IF(AND(DAY(B$1+6)>=$A2,DAY(B$1+6)<=$A2+6),30,""),IF(AND(DAY(B$1)>=$A2-6,DAY(B$1)<=$A2),30,""))

    of course if A2 is 29, 30 or 31 then in some months there won't be such a date so there wouldn't be a payment
    Last edited by daddylonglegs; 07-17-2010 at 01:00 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1