time problem

bronwyn

Board Regular
Joined
Jun 12, 2004
Messages
93
hi all, i have a simple staff rota, where hours are entered as 17:00-22:00 format, which denotes 5 hours. i need to be able to enter data from monday to sunday, which will calculate the amount of hours in each shift, and yield total hours at the end of the week, which is on a sunday. it should be able to ignore fields which are marked "off", to denote a day off, and highlight this cell red. I also have a possibility where the times will be logged as 17:00 to finish, so i will require to create a custom time which will have a value for times such as these. as always, any help appreciated.! x
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

bronwyn

Board Regular
Joined
Jun 12, 2004
Messages
93
sorry, also there is a possibilty that the times will cross over into the next day, so when i am calculating, the problem is that it is adding on 24 hours for times like 17:00-02:30. thanks again
 

bronwyn

Board Regular
Joined
Jun 12, 2004
Messages
93
i have attached file
rota.xls
BCDEFGHIJ
4NAMEMON 16TUES 17WED 18THURS 19FRI 20SAT 21SUN 22TOTAL
5STEPHEN18:00-2200off17:00-11:3017:00-finishoffoffoff00:00
6MHAIRI
7KAREN
8ANDY
9ANDREW M
10WILLIE S
11EWAN
12PAMELA
13STEPHEN M
Sheet1
[/img]
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

Here's something to get you started on the cross-day time calculations.
KEEPERS.xls
BCDEFGH
16MonTueWedThuFriActual
17Start time8:00 PM12:00 AM12:00 AM12:00 AM12:00 AMHours
18Finish time4:00 AM7:30 AM7:30 AM7:30 AM8:00 AMWorked
19Total hours8.007.507.507.508.0038.50
20Format all
21yellow cells
22above to
23"Number, 2
24decimal places",
25not to [hh]:mm
Time, Subtract


Here's another formula, a bit simpler. Use the formula in the formula bar, ignore the incomplete formulas in column D. The HTML Maker program thinks the "less than" sign is a HTML character and truncates the formulas:
KEEPERS.xls
ABCDE
110:00 PM7:30 AM9.5=(B1-A1+(B1< TD>Format: General
2-or-
310:00 PM7:30 AM9:30=(B3-A3+(B3< TD>Format: Custom, h:mm
Time, Subtract
 

bronwyn

Board Regular
Joined
Jun 12, 2004
Messages
93
hi thanks for response, is there any way to have the cels remain the same as in example, the way they are entered is as "1700-2200" for example
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

You're asking for trouble there. Look at your example: C2 is 18:00-2200 (without the second colon) and E2 is 17:00-11:30 (with both colons).

Excel is meant to do calculations based on entries in separate cells, not on text-based entries. I would strongly recommend changing your format to have start and end times in separate cells.
 

bronwyn

Board Regular
Joined
Jun 12, 2004
Messages
93
ok thanks, i will use your example, out of curiosity, can it be done if all formatting is the same?
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
If you're going to do further calculations using the total hours worked, such as multiplying it by a pay rate, I would suggest using General format.
 

Forum statistics

Threads
1,136,926
Messages
5,678,618
Members
419,776
Latest member
mikelowski

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
Top