Counting down days and hours

booiboa

New Member
Joined
Apr 1, 2002
Messages
25
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 //
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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 //
 
Upvote 0
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
 
Upvote 0
Hi Aladin,

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

Thanks for your help. Most appreciated!
// Boo //
 
Upvote 0
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
 
Upvote 0
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 //
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top