Results 1 to 10 of 10

Thread: Creating a Scheduling System in Excel for improving functionality and performance

  1. #1

    Creating a Scheduling System in Excel for improving functionality and performance



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everybody,
    I would like to create scheduling system in excel sheet. The following picture is attached for explanation.







    It should work as follows


    I have to make a schedule of the chapters and their completion date.


    The column A, B and C will be filled by me always. Lets say today is 4th aug 2015. So Now for Column E if the chapter's completion date is 28/07/15 which is passed then there should be written "pending by day 7", similarly for all cells. if date is yet to come or present date is there then in column E should be written "Date is due". This sheet should be daily updated by itself with respect to date mentioned in column C.


    Now column D should show icon set as shown, more the time pending more the black moon, lesser the time pending lesser the black moon, done or due date should be represented by full white moon


    Column F and G must have macro as shown. When i click "Done" then on the cell like on E2, E3, E4 etc. the text should change as shown in Column E depending upon on which date i clicked Done.
    If Done is clicked on the date for completion then write "Done on due date",
    if Done is clicked after the date of completion then write "Done .... days late" and
    if Done is clicked before the date of completion then write "Done...... days earlier.
    When i click Reset then again the text "pending by day...."should be re-written (erasing the text earlier in the corresponding cell E2, E3 and so on..


    Anything else like to display the performance on chart will also be appreciated
    Thank You, I will wait for your answer.....
    I hope i make myself clear....
    Last edited by amrinderminhas; 2015-08-06 at 02:04 PM. Reason: Macros, Charts, scheduling

  2. #2
    Kindly make a correction that in above picture The present date assumed is 06/08/2014. And in C9 cell the date should be 7/08/2015. So that means "date is due" as written in E9 cell.

  3. #3
    Kindly reply its important, if its not possible then just explain me the how the Done and Reset macro will work according to the condition i explained earlier..

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Armrinder, you should post the sheet, rather than a picture of the sheet.
    It would appear you already have some kind of conditional formatting for column D and this can probably be built upon for the rest of what you need.

  5. #5
    Hello Nolan,
    I am sending the excel sheet (this sheet is just a sample, actual sheet will be different for every subject), but the only difference will be in Column B & C. Consider each sheet to have 20 chapters or less. The column D that u mentioned is actually a little trick that i have done such that i hide the content of column D and only shown conditional formatting item. U will see that in excel file. The moons are only the way to see that how much days are passed. (this column D can altogether be eliminated if not resolved clearly, its not very important to have this column in my project)
    Moreover, focussing on E4 (where i have written Done 3 days earlier), and E7 (where text is Done 4 days late) cell, i havent applied any formula but just written manually which should actually means that I had hit "Done macro" at F4 cell on 25-07-2015 which means i have complted 3rd chapter earlier by 3 days. Similarly I have hit F6 macro on 05-08-2015 which means i have completed 6th chapter 4 days late. Similarly if hit Done macro on 08-08-2015 that means i have completed 8th chapter on 08-08-2015 (i.e.. on scheduled time).
    Lastly I want to have chart to see my progress w.r.t to Scheduled date for completion and how i am able to achieve it for each chapter.
    Thanx and hope to get some good news soon....

    I can send the file to email and i dont mind if its published here
    Attached Files Attached Files

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Sorry Arminder, I'm not able to assist you.
    Hopefully another member will help you out.

  7. #7
    Its ok NoSparks no problem..... It is just i have so much of belief in u that i can think of any unrealistic thing in the belief that it can be resolved by someone...if not then NoSparks is there at last... lol.....
    Anyways i wait for any other member in hope that this problem can be resolved.

    Iwould also like to state that its not important that problem be resolved actually in the way i mentioned. The whole idea is the purpose behind it.. i.e. ' A mini program that can tell me how much i am lagging or leading my schedule of completion and finally showing the progress in form of graph ' .

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello Amrinder

    I re-visited this and was a little disappointed no one helped you out.

    Had a bit of time so gave it a go.
    Have deleted your Sheet1, to remove all traces of existing conditional formatting, and used Sheet2.

    Not quite sure how to handle the column D icons when the Done button is clicked.
    Currently just turn them white.

    Hope you can get something useful from this.

    Good Luck with the project.
    Attached Files Attached Files

  9. #9

    Thanx NoSparks... a little hurdle left..

    Hello NoSparks, When there is all dark everywhere then a little spark is what just needed to rejuvenate the hope of day. I have lost all my hopes on that very day when u have said NO. What i felt is that its not that u cant handle this problem but may be u dont have enough time for it. Though i am disappointed earlier but my respect is for you is much more than that. Anyways The program worked perfectly. However There is one performance chart remaining. I have tried my best to make ur work as easy as i can in the attached sheet. I just want that chart to be displayed with Overall Performance written on one side, as shown in sheet. All the other tables beside that chart are only for ur reference and are actually not required to be shown. How to calculate Overall performance is also shown and written in the sheet. All I want is just the chart displaying the relevant data as shown in Attached Sheet. Hope I am not troubling u much.... .

    thanx
    Attached Files Attached Files

  10. #10
    Hello everybody, I am really eagerly waiting for the final touches to the above assignment. Although much have been done by NoSparks the little thing as i called performance charts remained to be created. I feel its not so difficult to make in comparison to what already has been achieved (again thanx to NoSparks). The logic to how to create these charts has been shown in the above attached sheet to the right of the chart. The problem is that i have manually entered those values to create the chart but in actual i want that these values to be picked up from the Column E on which macros are executed....
    Thanx, waiting for response...

Posting Permissions

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