Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

How do i calculate every 12 weekdays in a spreadsheet?

  1. Jun 13, 2012 #1
    hi, id like to know how do i calculate every 12 weekdays (mon. to fri.) via a spreadsheet? is there a formula i can use that will always give me the 12th weekday after every date one after another in one column?
    Last edited: Jun 13, 2012
  2. jcsd
  3. Jun 13, 2012 #2


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    Unfortunately there is not a specific function for every strange thing people want to do. You need to work it out your self. Sounds do-able but may take some time. You could explore the VB macro editor, assuming you can write a program.

    Good luck
  4. Jun 13, 2012 #3

    D H

    User Avatar
    Staff Emeritus
    Science Advisor

    If it wasn't for holidays it would be pretty easy. Add 16 days (2 days + 2 weeks), then add an additional two days if it is Thursday or Friday. Those holidays pretty much mandate writing a VB macro, however.
  5. Jun 14, 2012 #4
    Take a look at the WORKDAY function (under Date & Time functions). It returns the date serial number of the date before or after a specified number of workdays. It also allows for consideration of user-supplied holidays.
  6. Jun 14, 2012 #5
    thanks... then let's hope lotus symphony has it. as i've taken a liking to it recently...

    edit: thanks i figured it all out, just one more, how do i add the name of each day? ex. monday
    Last edited: Jun 14, 2012
  7. Jun 14, 2012 #6
  8. Jul 9, 2012 #7


    User Avatar
    Science Advisor

    I think this example should work:

    1) First column, put in a date, and make sure Excel recognizes it as a date (you can change the formatting later). e.g. 9-Jul-12

    2) In the second column, add 12 to the cell with the previous value. e.g. enter "=A1+12" (without quotes) in the new cell, assuming the first cell was A1

    You can just add and subtract days from dates in Excel (and you can copy and paste the "previous column + 12" formula to make things really easy).
    Last edited: Jul 9, 2012
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook