+ Reply to Thread
Results 1 to 10 of 10

Counting the days worked and days remaining on a schedule automatically

  1. #1
    Registered User
    Join Date
    08-11-2007
    Posts
    51

    Counting the days worked and days remaining on a schedule automatically

    I've saw an excel spreadsheet that did this before; however, I have no idea where to start on this. I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Hello uhcord98,

    Really, given your setup, to calculate this using formula(s) is possible but much trickier than it could be if your setup was different.

    I understand that you probably want to show the schedule in weeks as you have but it would be much easier if you had all the dates down column A and employee names across row 1. At the very least you need to use "true dates" in row 2, 11, 20 etc.

    Just so that I understand what you want....you talk about "the month"....are you referring to the calendar month or do you consider the whole 6 weeks shown to be a month?

    Would the current day be part of the days worked or days remaining?

  3. #3
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    I actually worded it wrong, I'm sorry.

    What I was looking for is to count the total number of scheduled work days (for each employee) for the month and display this data.

    In addition to this, I was looking for excel to see hey it is September 15th, only count the days from here on to display total days left to work for the month.

  4. #4
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    So I changed the format, I put the dates into column 1 and have the employees displayed in the rows going across.

    I put the formula in to count the total days worked, and I just need to figure out a formula to count the scheduled work days left for the month.
    Attached Files Attached Files

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =SUMIF(A2:A32,">"&B35,B2:B32)

  6. #6
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    hum, i couldn't get that to work. it just puts 0 in there.

  7. #7
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    Trying seeing my final example here to see if it helps any...thanks in advance i know this is a tough problem to solve.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    I've ALMOST got it figured out.

    What I did is made another sheet that has each date with how many days is left (on each date i put in a seperate formula that only counts from that day on). Lengthy to get going I know, but once the workbook is complete it will save me hours everyday.

    Now, all I need is a formula that will look at the date I enterted, go to sheet 3 and insert the value right beaneth that date. (So if I enterted "3rd" for today's date, it would go to sheet 3 and look at the value right under the "3rd").

    I posted an update workbook.
    Attached Files Attached Files

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by uhcord98
    hum, i couldn't get that to work. it just puts 0 in there.
    In your example it'll give zero because the dates you have are all in the past. If you put september dates in there then days to be worked in the future should be summed

  10. #10
    Registered User
    Join Date
    08-11-2007
    Posts
    51
    sweet, your right that does work. how exactly does the formula breakdown so i now how to use it for future reference?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1