• 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...

  • 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

J

jsr

New Member
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
 
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
 
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.
 
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!
 
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!!
 
You must log in or register to reply here.
Back
Top