Announcement

Collapse

Computer Lab Guidelines

Here in the computer lab, we talk about cool tech, the newest coolest gadgets, and tackle your toughest tech questions.

If you need to refresh yourself on the decorum, now would be a good time. Forum Rules: here
See more
See less

Excel and Pay Periods

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel and Pay Periods

    I need to be able to calculate the number of pay periods between two dates - and the pay periods are variable because it's for various people who don't have the same employer. I can get it to calculate the number of days but how do I get it to convert that into weeks so it can differentiate the different pay periods? Or is there a better way?


    "He is no fool who gives what he cannot keep to gain that which he cannot lose." - Jim Elliot

    "Forgiveness is the way of love." Gary Chapman

    My Personal Blog

    My Novella blog (Current Novella Begins on 7/25/14)

    Quill Sword

  • #2
    Originally posted by Teallaura View Post
    I need to be able to calculate the number of pay periods between two dates - and the pay periods are variable because it's for various people who don't have the same employer. I can get it to calculate the number of days but how do I get it to convert that into weeks so it can differentiate the different pay periods? Or is there a better way?


    It's coming together in my head, along with part of the formula which will ask how many pay periods in the year, to know how many weeks/days......
    The first to state his case seems right until another comes and cross-examines him.

    Comment


    • #3
      will you, perhaps, have a column for start date, end date, and number of pay periods per year?
      The first to state his case seems right until another comes and cross-examines him.

      Comment


      • #4
        Start, end, type of pay period, number of pay periods, gross pay per pay period and the total pay for the interval.

        Yes.
        "He is no fool who gives what he cannot keep to gain that which he cannot lose." - Jim Elliot

        "Forgiveness is the way of love." Gary Chapman

        My Personal Blog

        My Novella blog (Current Novella Begins on 7/25/14)

        Quill Sword

        Comment


        • #5
          so, pay periods could be....

          1st and 15th
          every other Friday
          once a month

          15th and last....

          do you have a finite number of "types" of pay periods?
          The first to state his case seems right until another comes and cross-examines him.

          Comment


          • #6
            and would the start date and end date necessarily be paydays? Or... what else might the start and end dates be?
            The first to state his case seems right until another comes and cross-examines him.

            Comment


            • #7
              Bi-monthly, bi-weekly, weekly, monthly and annual (that one's easy, at least). I was going to include 'daily' but that seems silly.

              The start/end dates are employment so they may or may not conform to the pay period. We need an estimate of income for short duration jobs.
              "He is no fool who gives what he cannot keep to gain that which he cannot lose." - Jim Elliot

              "Forgiveness is the way of love." Gary Chapman

              My Personal Blog

              My Novella blog (Current Novella Begins on 7/25/14)

              Quill Sword

              Comment


              • #8
                Originally posted by Teallaura View Post
                Bi-monthly, bi-weekly, weekly, monthly and annual (that one's easy, at least). I was going to include 'daily' but that seems silly.
                OK, I think the first thing to do is convert the pay period (whatever it is) to the equivalent of an annual salary divided by the number of work days to get a daily rate.

                Then use the NETWORKDAYS function to count the number of work days between the start and end dates, times the daily rate.

                The start/end dates are employment so they may or may not conform to the pay period. We need an estimate of income for short duration jobs.
                Can we assume they are working "work days", and not weekends and holidays?
                The first to state his case seems right until another comes and cross-examines him.

                Comment


                • #9
                  I like that - but no, we can't.
                  "He is no fool who gives what he cannot keep to gain that which he cannot lose." - Jim Elliot

                  "Forgiveness is the way of love." Gary Chapman

                  My Personal Blog

                  My Novella blog (Current Novella Begins on 7/25/14)

                  Quill Sword

                  Comment


                  • #10
                    Originally posted by Teallaura View Post
                    I like that - but no, we can't.
                    Hmmmm
                    The first to state his case seems right until another comes and cross-examines him.

                    Comment


                    • #11
                      Originally posted by Teallaura View Post
                      I need to be able to calculate the number of pay periods between two dates - and the pay periods are variable because it's for various people who don't have the same employer. I can get it to calculate the number of days but how do I get it to convert that into weeks so it can differentiate the different pay periods? Or is there a better way?


                      OK, so we really don't need to know the number of paydays, right?
                      If we know the number of work days between start date and end date, and we know the rate of pay per day, that makes it pretty easy, yes?

                      (yeah, some of this is rehash)

                      So, the NETWORKDAYS function uses the start and end dates to calculate how many actual work days there are.

                      Once we determine what the "daily rate" for a particular pay type is, we have an estimate (plus overtime/holidays, etc.)
                      The first to state his case seems right until another comes and cross-examines him.

                      Comment


                      • #12


                        there are lots of paycheck calculators on the web if that helps.

                        here is a site that might help too:

                        http://chandoo.org/wp/2008/09/17/pay...xcel-formulas/

                        Comment


                        • #13
                          Originally posted by Teallaura View Post
                          Bi-monthly, bi-weekly, weekly, monthly and annual (that one's easy, at least). I was going to include 'daily' but that seems silly.

                          The start/end dates are employment so they may or may not conform to the pay period. We need an estimate of income for short duration jobs.
                          You can't just divide by number of days? 15 for bi-monthly, 14 for bi-weekly, 7 for weekly, 30 for monthly? Move to next largest number for incomplete periods.
                          I'm not here anymore.

                          Comment


                          • #14
                            one other consideration Teal...

                            Start date doesn't always determine the first pay date. Many places will want to sync the employee up with their standard pay day, so if you start the week of a payday, you would have to wait three weeks to get paid and from that point you would get paid every two weeks on the same day everyone else does.

                            Comment


                            • #15
                              Originally posted by Teallaura View Post
                              I need to be able to calculate the number of pay periods between two dates - and the pay periods are variable because it's for various people who don't have the same employer. I can get it to calculate the number of days but how do I get it to convert that into weeks so it can differentiate the different pay periods? Or is there a better way?



                              Give me some sample data and what you want it to look like. I can put something together using VBA in Excel if someone doesn't beat me to it.
                              That's what
                              - She

                              Without a clear-cut definition of sin, morality becomes a mere argument over the best way to train animals
                              - Manya the Holy Szin (The Quintara Marathon)

                              I may not be as old as dirt, but me and dirt are starting to have an awful lot in common
                              - Stephen R. Donaldson

                              Comment

                              Related Threads

                              Collapse

                              Topics Statistics Last Post
                              Started by Ronson, 03-20-2024, 07:20 PM
                              2 responses
                              28 views
                              0 likes
                              Last Post rogue06
                              by rogue06
                               
                              Started by Christian3, 03-15-2024, 10:15 AM
                              13 responses
                              64 views
                              0 likes
                              Last Post QuantaFille  
                              Working...
                              X