1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

date formula to calculate bi-weekly pay period

Discussion in 'Ask an Excel Question' started by jsr, Mar 5, 2013.

  1. jsr

    jsr New Member

    Messages:
    2
    Hello

    new to this site. I have constructed a spreadsheet for my personal time off accrual. I have a column for the date range, for example, 2/10/2013-2/23/2013 is the first row. How do I construct a formula to compute the next date range 2/24/2013-3/9/2013? I have taken a few excel courses and know how to formulate monthly loan payment date. But this issue was not taught in class. I do not really need the answer (although that would be too easy), but the logical thinking of the construction of the formula.

    thanks in advance
    Lisa Eaton likes this.
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,524
    Jsr


    Firstly, Welcome to the Chandoo.org Forums


    My recommendation is never hard code dates like that, always use date numbers & formulas


    So I would have two cells

    one would have 2/10/2013, lets assume that it was A1

    From this you can calculate the second date B1: =A1+13


    You can now see that

    A2: =A1+14

    B2: =B1+14

    etc


    If you really need to you can combine them like: =A1&" - "&B1

    which will display 2/10/2013-2/23/2013


    Dates are simply stored as numbers in excel and so doing maths is quite simple

    The dates are stored as Integer Numbers starting with 1 as 1/1/1900

    Today is 413838


    Displaying Numbers as Dates is achieved by using a Custom number format on the cells involved

    Excel automatically works that out as Dates are typed in
    Lisa Eaton likes this.
  3. jsr

    jsr New Member

    Messages:
    2
    Thank very much! That was too easy. What I needed to do was to break down the problem and focus on the result I wanted. Your assistance on this issue will surely help me in the future.
  4. Lisa Eaton

    Lisa Eaton New Member

    Messages:
    2
    I've been looking for a simple breakdown of how to create the biweekly pay periods. I was able to follow your example until the display of the date range. My data is showing up as an integer - integer. I tried to format the individual cell with a date format but it didn't work for me. I'm guessing that is because I want the formatting of the date range with the hypen included. Please help! Thanks!
  5. Lisa Eaton

    Lisa Eaton New Member

    Messages:
    2
    Whoohoo! With your direction about what I wanted to do was called ("custom number formatting"), I was able to find the information to solve my problem. This is a great website to get answers and direction to answers quickly! Thanks so much!!

Share This Page