Time calculation for a givenn period

Hello everyone,

Please consider the following scenario: I have a set of resource for which 
the total resource hours remaining (A1) for a year (Jan to Dec) has been 
calculated by subtracting the scheduled hrs. from the total hrs. Lets say I 
have project start date as "May 1, 2005' and end date 'April 30, 2006' and 
from this I can find the time in hrs between these two dates. Now I have to 
find the resource availability for this period. I know the resource 
availability for the present year (A1) (that is from May 1 to Dec 31). But I 
need to know the resource availability till April 30, 2006. I understand that 
I should add the resource availability for this year and resource hrs for the 
period Jan to April of 2006. I was wondering whether I can get a formula to 
automate it since I will be dealing with many projects which may begin and 
end at any date of the year. Please help me out. Thanks for your suggestion 
in advance.

KT
0
KT (80)
4/27/2005 3:58:11 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
336 Views

Similar Articles

[PageSpeed] 9

Hi KT

the networkdays function will give you the number of working days between 
two dates (inclusive) - where a working day is considered monday to friday. 
So, if you calculated the number of working days between your project start 
& finish (or whatever time period is specified) and then subtracted the 
number of public holidays, the number of days annual leave etc and then 
multiplied the remainder by the number of hours in a "day" you should end up 
with the information you want.

information on NETWORKDAYS from help ---

Returns the number of whole working days between start_date and end_date. 
Working days exclude weekends and any dates identified in holidays.
If this function is not available, and returns the #NAME? error, install and 
load the Analysis ToolPak add-in.

the syntax is:
NETWORKDAYS(start_date,end_date,holidays)
-----------
having said that, i would personally suggest investigating MS Project to see 
if that is a better fit for your needs than Excel.

-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"KT" <KT@discussions.microsoft.com> wrote in message 
news:2B1256A7-300B-4019-999E-8C751E46750B@microsoft.com...
> Hello everyone,
>
> Please consider the following scenario: I have a set of resource for which
> the total resource hours remaining (A1) for a year (Jan to Dec) has been
> calculated by subtracting the scheduled hrs. from the total hrs. Lets say 
> I
> have project start date as "May 1, 2005' and end date 'April 30, 2006' and
> from this I can find the time in hrs between these two dates. Now I have 
> to
> find the resource availability for this period. I know the resource
> availability for the present year (A1) (that is from May 1 to Dec 31). But 
> I
> need to know the resource availability till April 30, 2006. I understand 
> that
> I should add the resource availability for this year and resource hrs for 
> the
> period Jan to April of 2006. I was wondering whether I can get a formula 
> to
> automate it since I will be dealing with many projects which may begin and
> end at any date of the year. Please help me out. Thanks for your 
> suggestion
> in advance.
>
> KT 


0
JulieD1 (2295)
4/27/2005 4:04:44 PM
Reply:

Similar Artilces:

Activities Date and Time
Hi, I read somewhere a while back that there was a way to customize the Activities List view to display the date and time it was created. Something about putting it into the subject field somehow, or something like that? Could someone please let me know how to do this as we are converting thousands of activities from Goldmine and as the Activities Forms cannot be modified, it is hard to find a specific activity by a date. Once a historical activity is opened the Due Date was the Create date for our migration as was the time, but is does not show in the list. We could add that to the...

sends the same email several times
my recipients are getting several copies of the same email I send Help "PJ" <PJ@discussions.microsoft.com> wrote in message news:B9C90285-B85C-4FEF-BDCE-A267A9428735@microsoft.com... > my recipients are getting several copies of the same email I send Help Are the emails normal small mail or do they have large attachments? If the former then check that you do not have your account set up more than once, Tools Menu>Accounts. If the later then is WM set to break apart messages larger than a certain size? Check for this on the advanced tab of your...

Run time error 91 #2
Has anyone else experienced this problem. At the end of a transaction we will get a message 'run time error 91 - object variable or with block variable not set' - the program freezes and we have to exit and log back into POS. The transaction posts okay so we don't lose any data but it is annoying. We have tried to find the logic thread - ie time of day, customer, product, terminal - but there seems to be no common theme. We are running SO and HQ version 2.0.2.0115. Thanks -- Merle Check your receipt template..... "Island Blue" <IslandBlue@discussions.micr...

Convert Date To Time Back To Date
Hi, I know the subject line is too confusing. I didnt know how to explain this so I used what I want. I had some one do some data entry work for me sometime back. Now this guy, used his own brains at added time in the format of MM:DD:YYYY instead of MM/DD/YYYY. Now once that is done, as the field was set to store date, it converted the MM:DD:YYYY to some date, other than the actual one. So I now I have completely new date with me. Now I need to take it back to original date. And I have no clue on how to do it. e.g. Actual Date - 04/08/06 Entered As - 04:08:06 Visible As - 12/30/1899 No...

how many times ... identical email to 150 contacts
Hi, I would like to know how many times an email gets sent out of exchange 2000 (via our link 512kb - ADSL) if you send 150 emails with an attachment of (2MB). I am ending 1 identical email message + identical (2mb) attachents to 150 contacts. My question is will 150 X 2MB (300 MB) get sent out through our pipe or does it function like the single instance message storage (if yes) does this have to be enabled? Please note that most of the emails that get sent out are to contacts who are not a user on there (their mailbox isn't on this server). Irene In the last exciting episode, &qu...

Date calculation on 2 fields
I have a query that calculates captures a date filed 20 days after the date which is entered. It then gathers all the dates after the 20 days has been calculated. I need to code another date field that doesnt grab info 45 days past the date. Example: works now user - puts in date 1/28/2010 query - subracts 20 days from that date = 1/08/10 query - then gathers all the dates after 1/8/10. Need - To look at another date field and make sure no data is gathered 45 days from the date in field. Are you talking about two separate queries, or a single query with two criteria? ...

Counting time ranges
I have a spreadsheet that looks like this... 07:51:4 07:56:2 07:59:5 08:36:0 08:36:2 09:04:2 09:08:1 09:12:2 09:13:3 09:15:4 09:17:3 09:17:3 09:20:1 09:23:1 09:24:1 I need to be able to count how many times each hour shows up, I tried using count if but the range isnt working? Please help!!! Cathy Hi maybe something like =SUMPRODUCT(--(A1:a100>=9/24),--(A1:A100<10/24)) to count the numer of 9 hour occurences >-----Original Message----- >I have a spreadsheet that looks like this.... >07:51:40 >07:56:27 >07:59:59 >08:36:01 >08:36:24 >09:04:25 >09:08:18 >09:...

How to show a cumulative value over time in a pivot chart.
hello, I currently have a spreadsheet with (among other things) the following columns: year, date, distance I want to create a pivot chart that shows the cumulative distance over time by year. I can easily create a bar chart with this information now and show each year in a series, but i'd like to figure out how to show a rolling year-to-date total. Any help would be greatly appreciated. No takers? In case I wasn't clear, I want to show months or weeks on the x axis and then distance on the y axis. I want a series for each year in my data set and I want the value to be cumulative...

Display records one time only
Primary Key is ID and data is extracted by ClubID. Fields are ID, ClubID, StpMail, FNm, LNm, MI, AD1, CO, Cty, ST, ZP. Prompt asks for up to 5 ClubID's, but the resulting Dataset has duplicates (one ID may have been a member of all ClubID's) ClubID is a year value (i.e., 2007). How do I restrain the data so I don't end-up with Duplicate values? Thanks in advance for any suggestions. -- FirstVette52 No duplicate ID's regardless of the ClubID. -- FirstVette52 "FirstVette52" wrote: > Primary Key is ID and data is extracted by ClubID. Fi...

How To Select Multiple Transactions At One Time
I would like to delete a block of transactions from an account in Money 2007 Plus Deluxe. Is there away to select multiple transactions and then delete all of them or must I do each one, "one-at-a-time" ? In microsoft.public.money, Snoopy328 wrote: >I would like to delete a block of transactions from an account in Money 2007 >Plus Deluxe. Is there away to select multiple transactions and then delete >all of them or must I do each one, "one-at-a-time" ? The later. You could get some kind of keyboard macro if you are doing really a lot of these. Otherwise, c...

Display currency as calculated
Gyus I am just about finished but I can't get Excel to display my currency as the calculated result. I need to display this accurately so I can take the figures to my accountant. My issue is that when I calculate the GST on an amount, the calc is fine but is displayed in correctly eg $22.63 / 11 = 2.05727272727273, so I need it to display it as $2.05 not as it does - $2.06 (I am sure the taxman would like this printout). I looked at the TRUNC command but can't get it to work. Is there a way to do this in code to a column of numbers where every row has been summed(horiz...

How do I time/date stamp an entry in a note from a calendar entry?
Using Outlook 2003. I have calendar entry "Call so and so...." I have called this person before and want to time date this call above the other one. I can enter it manually however other programs I have used wil do this automatically. Surely Outlook can do this? in outlook 2003 you need to use a utility or enter it manually. You can also use VBA to do it. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.out...

HQ CLient 2.0
I am setting up a new polling server with RMS HQ Client 2.0, when I click on the icon. I am getting a Run Time Error 339 and it keep referencing a file call RICHTX32.OCX. Have anyone ran into this or know how to correct this? Ed I figure it out. "Ed" wrote: > I am setting up a new polling server with RMS HQ Client 2.0, when I click on > the icon. I am getting a Run Time Error 339 and it keep referencing a file > call RICHTX32.OCX. Have anyone ran into this or know how to correct this? > > Ed ...

Set a minimum row height a the same time with autofit row height
I want to set a minimum row height, but allow autofit for anything that is greater than that height. Is this possible? i.e. If i have a cell with one line of text, then I'd like to see the height as 50, as opposed to what the autofit would normally set it as, say 25. Then for any additional lines, autofit will make more than 50 to fit the lines. You could use a macro to autofit the row, then check to see if the rowheight is smaller than your limit and adjust if necessary. But you may find that it's simpler to put some text in column A that can serve as the limiting...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Calculating Hours
I am looking to calculate hours and minutes. I want to calculate how long something has been running for by adding hours and minutes every week to the total. The problem is it stops counting after about 1000hours. is there a formula to count hours and minutes up to say 10000hours. It I put 500:45:00 in a1 and &00:45:25 in A1 The in A3 =A1+A2, I get 1201:30:25 I do not understand "it stops counting after 1000 hours" please give more details of your worksheet's layout best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from ema...

Excel 2002 copying and pasting some time hangs using key strokes
It happens sometimes: In excel 2002,the paste button will be grayed out if you use key strokes, not mouse to copy and paste. Only way to go to resolve is to Crtl-alt-del. Is any one has resolution on this. Or information why this happens. Dr Tim ...

Can I make multiple excel workbooks populate information to one main wrkbk real time?
To give you an idea of what I am trying to accomplish... I run a mortgage company and I have about 16 loan officers I personally manage. Currently each of them keep their own excel pipeline of loans in progress in their personal folder on the server, of which I have to go into each one to check on. I can design the new pipelines however here is my main goal. I'd like to create one large read only (to them) pipeline with all the loan officers names in it, along with the appropriate info to the loan. I would like to create personal pipelines for each of them to input their data into. I ...

Posted this several times, Could someone please help??
Hi, I have been receiving an intermitted error message when I open up an email within Outlook 2000. At the time when I receive this message I do not have many windows open. This is the Error I get: Can't open this item. The text formatting command is not available. It may not be installed correctly. Please reinstall Outlook. Out of memory or resources. Please close all windows. My computer is running Outlook 2000 and it has the current updates installed on it. I have boosted the paging files. In the International Options under Mail format I changed both the send this en...

Enter/Macth
General usage question has come up from our office that I can't find any information on online and wanted to ask the rest of you. Situation: Program: MS Great Plains - 8.0 Standard Enter/Match posting to a prior period that has been closed - Invoice for January 2006, which was accrued, arrived in February and needs to be enter/matched using the invoice date of January. How can it post in February and what affect does it have? Steps: 1) Shipment made with "Transactions -> Purchasing -> Receivings Transaction Entry (January 28, 2006) 2) Invoice for shipment arrives Februa...

Real time failover
Hello, We are looking in to a failover solution, and are leaning away from going to Microsoft Clustering, because we are already up and running on a windows 2003 standard network with SQL 2000, and we do not want to re-make our entire structure. Does anyone have any recommendations? We would like disaster failover, preferably automated. Does anyone have any experience with any of the third party utilities that accomplish this? Thank you for your time. ...

Calculating Total
Hi there, I run the following code: Private Sub FShare__Coke_2LtPET_Exit(Cancel As Integer) [FShare: Total].Value = [FShare: Total].OldValue - [FShare: Coke 2LtPET].OldValue [FShare: Total].Value = [FShare: Total].Value + [FShare: Coke 2LtPET].Value Form.Recalc If [FShare: Coke 2LtPET].Value = 0 And [Price: Coke 2LtPET] <> 0 Then result = MsgBox("Price info entered - needs F Share info", vbOKOnly) Cancel = True End If End Sub However, for some reason the result blanks out any information in this field after this fun...

Calculating the average of cells
I am trying to use a formula to calculate the average of random cells currently containing data, however also including cells where data will be added at a later date but remain empty. Is this possible? On Mon, 13 Mar 2006 12:53:27 -0800, pippa <pippa@discussions.microsoft.com> wrote: >I am trying to use a formula to calculate the average of random cells >currently containing data, however also including cells where data will be >added at a later date but remain empty. Is this possible? =average(<range1>,<range2>...) will work quite happily with blanks and no...

Calculating specifc byte positions in cells
Hi, Here is what I am trying to do: I have a spreadsheet that keeps track of employees vacation and sick time. The spreadsheet is formatted to column A is the employee name, columns B - AB are days of the month. I would like to have the manager be able to enter in the time off as either S8 or V8 (8 representing the number of hours off the employee had S = sick V = vacation). I would like Column AC to then calcuate the total sick hours and AD to calcuate the total of vacation hours. I played around with a few formulas but could not figure out a way to do it. Is there a way to have this d...

Dates and times
I have 2 columns labeled Date and Time. I used the format mm/dd/yyyy and found that 3/14 gives me 03/14/2003. So far so good. But I want excel to enter the slashes for me. I suspect a custom format would do this, but Excel is not very specific as to how to set one up. Similarly, for times. I want them on a 24-hr clock and found a format for this but whwn I enter a time all I get is "00:00" [to say nothing of a date in the formula bar which is a) not the correct date; and b) not needed in the timee column anyway]. I assume this has something to do with Excel assuming ...