combine workday and time functions

OK, we're listening. You can speed this along by presenting a complete scenario with sample data, sample results mocked up manually.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Originally Posted by jmac1947

My problem is that the values I was originally told were days wheras the contract values (thanks lawyers for your help!!!!) now talk in hours (2 hours, 4 hours, 12 hours, 20 hours, 80 hours).

John, I'm still a little unclear but I'm guessing the above means you will be looking to adjust your "breakfixacknowledge" table such that the value are time based rather than day based, is that correct ?

Assuming that is the case... ie that you're now looking to add hours to a start date time where hours must be working hours then I suspect you should be able to adapt something along the lines of the below:

Please Login or Register  to view this content.

where:

F5 is your start datetime (presently just date)

VLOOKUP determines Time available based on Priory (ie switching from Day integer to Time allocation - eg 30:00 rather than 3 days)

D2 is start of working shift (08:00)

E2 is end of working shift (18:00)

you can modify the references as required of course...

Of course the main point would be that your Start Date should really become a Start DateTime - and I guess we would assume the Start Time to be within the working hour boundaries (ie between 08:00 and 18:00)

Is the above along the right lines ?

To elaborate by means of example...

If we assume the lookup value for severity 2 changes from 3 to say 30 hours and that the start datetime for your first incident becomes 13-Jan 09:00 (ie we add the time) ... then using the above formula the max completion datetime would become: 18-Jan 09:00
(we would use 9 hours on the 13th, 10 hours on the 14th, 10 hours on the 15th (Fri) and then 1 hour on the Monday)

It might be an idea to post an example which elaborates on the Hour element of your prior post - this is obviously the fundamental issue.


Note: the above is c/o daddylonglegs our resident genius on all things date-related - if you search with him as user and WORKDAY etc as search term you will find a veritable goldmine of examples.

jmac1947,

Time in XL is Decimal ie Noon = 0.5 as 6am = 0.25 and 6pm = 0.75 etc... (you can "see" this if you enter say any of 12:00, 06:00, 18:00 into a cell and format to General)

Presently in your constants table you are, as you say, storing "hours" as Integers rather than as true Time values (decimals)
Using your file, you presently have 4 for Priority 2 which equates to 4 days or 96 hours - hence the current result on the analysis sheet.

To calculate correctly you therefore have 2 basic choices:

1 - store Hour values as true Time values in the Constants table
eg enter 4:00 into the cell rather than 4

2 - keep hours as Integer and adjust the actual calculation to divide each VLOOKUP value by 24

Needless to say option 1 is the one you should pursue else you're adding more & more calcs. to an already complex calculation.

To demo. using the example in my prior post - should you change 4 to 30:00 for Priority 2 you will find the result in G5 on your data sheet matches result outlined previously - ie 18-Jan 09:00

Note:
assuming you do indeed revert to storing time values in the constants sheet, format the time cells as [h]:mm (the [ ] ensures cumulative hours are displayed - once beyond 24 hours)

I'm glad it's working for you...

I can't take the credit I'm afraid - as mentioned earlier on in the thread the formula you're using is c/o of another member here - daddylonglegs - all credit must go to him - I for one don't know how he thinks them up !