Counting down days and hours

[booiboa]

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 //

[Ian Mac]

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 ]

[booiboa]

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 //

[Aladin Akyurek]

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

[booiboa]

Hi Aladin,

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

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

[Ian Mac]

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 ]

[booiboa]

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 //

[Ian Mac]

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.