Results 1 to 5 of 5

Thread: Identifying date intervals depending on a variable
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Identifying date intervals depending on a variable

    Dear Excel community

    To start with, I am an Excel novice - please forgive me any naive questions.

    Context
    Column A contains dates in ascending order - each row for a new day.
    Cell C1 contains a variable as text string, which can either be "weekly", "fortnightly", or "monthly".

    Problem
    Depending on the variable chosen in C1, I wish to have a formula in column B that identifies weekly, fortnightly, or monthly intervals from the starting date in column A.
    Basically, column A shows a date, and column B indicates next to it whether this date is a "payday" or "--".

    Thanks for taking time to help me.

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,642
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Identifying date intervals depending on a variable

    You forgot some of the rules. What day is a payday on for weekly? Same for fortnightly but you will also need to supply a date that is a payday so the date in column A can be tested if it is the correct week for paypay. What day of the month is payday on monthly pay?

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identifying date intervals depending on a variable

    Hello Steve - thanks for your response.

    The payday will change in relation to the starting date. Let's say the starting date is 6/6/19, then I'd like to identify every 7th day, 14th day, or monthly interval from there on. If the starting date changes so will the paydays.

    Monthly should take into account the varying number of days for each month. In our example, I'd like to highlight 6/7/19, 6/8/18, 6/9/19 and so on.

    Thank you

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,642
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Identifying date intervals depending on a variable

    Ok this will base all results based on cell A1 that should have your first date:

    =IF(OR(AND($C$1="Monthly",DAY($A$1)=DAY(A1)),AND($C$1="Fortnightly",MOD(A1-$A$1,14)=0),AND($C$1="Weekly",MOD(A1-$A$1,7)=0)),"Payday","-")

  5. #5
    New Member
    Join Date
    Feb 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Identifying date intervals depending on a variable

    That's fantastic - thank you so much!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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