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. ### jsrNew 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.

Lisa Eaton likes this.
2. ### HuiExcel NinjaStaff Member

Messages:
10,695
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. ### jsrNew 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 EatonNew 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 EatonNew 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!!