# Calculate time durations for a roster, and indentify the earliest and latest times

1. ## Calculate time durations for a roster, and indentify the earliest and latest times

i have to record time durations for a roster that starts at 07:00 and ends 06:00. please see attached sheet.

I NEED A FORMULA TO POPULATE THE "START" AND "END" COLUMN

I AM CONSIDERING THIS PARTICULAR LAYOUT BUT IT IS NOT SET IN STONE

2. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Hi bajdr47

Not sure what you mean!

3. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Pl see the attached file.

4. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Sorry Gentlemen,

The roster starts at 07:00 and ends 24hrs later 06:00.
i need to read the time order from 07:00-06:00, not 00:00 to 24:00.
Also the start and end time will not always start or end on the latest or earliest time

Regards

Baj

5. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

i am badly stuck in the sheet attached "ROSTER" i need to examine an indeterminate number of time periods and indentify the earliest and latest time ie EARLIEST = STARTTIME and the LATEST ENDTIME. the difficulty is that the time has to be calculated where the earliest time is 07:00 and the latest time is 06:00. As the Roster starts at 07:00 not at midnight

6. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

The roster starts at 07:00 and ends 24hrs later 06:00
That's 23 hours later.

Perhaps you could post an updated example, and explain clearly in context what the problem is.

7. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Sorry again,

i hope that this example is easier to understand.

Regards

baj

8. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

What aren't the tasks recorded squentially?

What is the problem with what you have?

9. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

In the START and END column, i have to manually input the times by checking Cloumns B E and H, i was wondering if there is a formula that will automate the selection in Column J and K

10. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Sorry SHG,

the tasks come in from job sheets, and i don't have control of the inputting process.

11. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Shifts run from 07:00, but ann comes in at 6:00?

12. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Sorry that should have read 08:00

---------- Post added at 08:28 PM ---------- Previous post was at 08:25 PM ----------

Dont always have my glasses on....But i need to always have my glasses on

13. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

K2: =MIN(MOD(C2-"7:00",1), MOD(F2-"7:00",1), MOD(I2-"7:00",1)) + "7:00"

L2: =MAX(MOD(D2-"7:00",1), MOD(G2-"7:00",1), MOD(J2-"7:00",1)) + "7:00"

Copy down.

14. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Many thanks SHG,

There is a problem if any of the tasks are left empty, i adapted your formula as below, it seems to be ok.
=MIN(IF(C4="",0,MOD(C4-"7:00",1)),IF(F4="",0,MOD(F4-"7:00",1)),IF(I4="",0,MOD(I4-"7:00",1)))+"7:00" IS this OK.or would you have altered it differently.
If i could be so bold to ask, but i understand in principle how the MOD function works, but would you mind explaining about the use of the 07:00 and particularly the double quotes.

Again you assistance is very much appreciated

BAJ

15. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

Your shift starts at 7:00. I subtracted 7 hours from all of the times (effectively moving the day back 7 hours), got the minimum, and then added the 7 hours back.

"7:00" is a string that looks like a time. When it's part of an arithmetic expression, it gets coerced to the number value for 7:00.

16. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

SHG

Sorry but how should i record a task that ends at the close of the shift eg 04:00 - 07:00 It doesn't seem to register.

Kind Regards

BAJ

17. ## Re: Calculate time durations for a roster, and indentify the earliest and latest times

You would be doing yourself a favor if you posted example workbooks.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1