Counting down days and hours
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Counting down days and hours

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I use a excelsheet for keeping track of hy projects. THere I have a row for budgeted hours, one for used hours, and one for hours left to use.
    Every week I manually have to calculate how many days/hours there are left on the calendar, to see if we need more resources or not.

    Is there a way to set starting date and stop date and get the total of working hours? Also so it automatically updates how many days/hours there are left by looking at todays date and stop date?

    Any ideas anyone?
    Thanks,
    // Boo Engstrand //

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try This, NOT very elegant, but may get you started,

    1) put your start date/time in A1
    2) End time in B1
    3) in C1 put =NOW()
    $) in D1 put =LEFT(B1-C1,FIND(".",B1-C1)-1)&" Days "&TEXT(RIGHT(B1-C1,LEN(B1-C1)-LEN(LEFT(B1-C1,FIND(".",B1-C1)-1))),"h:mm")&" Hours"

    Note The start time is not important in the calculation BUT you can adapt the formula to return time elalaped.

    Also, for the number of hours change the Format of the cell to Custom [h]:mm

    hope this helps
    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-04 01:35 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for your answer!!

    Your formula got me the amount of days and hours between start and stop date. There is a function called workdays (or something). Is there anyway I can use that to eliminate weekends, holidays, and then multiply the result with 8 (working hours in a day)??

    // Boo //

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,798
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-04 00:27, booiboa wrote:
    I use a excelsheet for keeping track of hy projects. THere I have a row for budgeted hours, one for used hours, and one for hours left to use.
    Every week I manually have to calculate how many days/hours there are left on the calendar, to see if we need more resources or not.

    Is there a way to set starting date and stop date and get the total of working hours? Also so it automatically updates how many days/hours there are left by looking at todays date and stop date?

    Any ideas anyone?
    Thanks,
    // Boo Engstrand //
    Consider the following whic is in A1:E2.

    {"budgeted hours","start date","end date","hours used","hours left";
    90,37297,37312,90,0}

    A2 houses total budgeted hours entered manually.

    B2 houses the project start date entered manually.

    In C2 enter:

    =WORKDAY(B2,A2/8)

    that computes a workday date on which the project ends (that is, budgeted hours will have to be used). Note that a working day counts 8 work hours.

    In D2 enter:

    =IF(TODAY()>C2,A2,NETWORKDAYS(B2,TODAY())*8)

    that computes hours used up to today.

    In E2 enter:

    =A2-D2

    which computes the hours left at any moment in time.

    Note. WORKDAY as well as NETWORKDAYS are available thru the Analysis Toolpak (which you can activate via Tools|Add-Ins, if needed). Also, these two functions allow you to take a pre-specified set of holidays. Consult the Help file for this feature.

    Aladin

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin,

    Worked fine!!
    My calculator gives you his regards too!!

    Thanks for your help. Most appreciated!
    // Boo //

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I realise you have a answer but I thought I throw this idea into the pot, I like the idea of putting a target number of hours into the mix.
    going along those lines

    in A1 target hours
    B1 Start Date
    C1 =WORKDAY(B1,A1/8)
    And D1 =IF(C1use a less than signNOW(),"Overdue by "&(NETWORKDAYS(NOW(),C1)+1)*(NETWORKDAYS(NOW(),C1)+1)&IF((NETWORKDAYS(NOW(),C1)+1)*(NETWORKDAYS(NOW(),C1)+1)=1," day "," days ")&TEXT(NOW()-"9:00","h:mm")&" Hours",NETWORKDAYS(NOW(),C1)-1&" days "&TEXT("9:00"-TEXT(NOW()-"9:00","h:mm"),"h:mm")&" Hours Remaining")

    this will give the Days and Hours based on a starting day time of 9:00 (change as needed), and also give the amount of time overdue.

    An interesting thing re: this question is, what if you plan to start a job at 12:00 and it will take 27 hours to complete, the expected time of completion would be 15:00 the next working day. ny formula doesn't answer that question but could be adapted if needed.

    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-04 08:20 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ian Mac,

    I did not follow you there:
    ---------------------------
    in A1 target hours
    B1 Start Date
    C1 =WORKDAY(B1,A1/8)
    And D1 =IF(C1

    ---------------------------
    Did something fell off??

    For my use of this I'm not concerned when on a day the work is finished or started. I'm just calculating that everybody does their 8 hour day. But it is an interesting question.

    Regards,
    // Boo //

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-04 04:21, booiboa wrote:
    Hi Ian Mac,

    I did not follow you there:
    ---------------------------
    in A1 target hours
    B1 Start Date
    C1 =WORKDAY(B1,A1/8)
    And D1 =IF(C1

    ---------------------------
    Did something fell off??

    For my use of this I'm not concerned when on a day the work is finished or started. I'm just calculating that everybody does their 8 hour day. But it is an interesting question.

    Regards,
    // Boo //
    Again I know you have your answer but it took me while, check the above post again, I can't seem to disable the Http on here. It will be my fault.
    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com