Schedule Worksheet, Issue with displaying Hours worked as general number

NeoStylez

New Member
Joined
Oct 26, 2010
Messages
3
Hello,

I am trying to accomplish the following in a single excel sheet.

-Display a one week working schedule
-Calculate Hours worked for shift and subtract 30min lunch for shifts over 5.5 hours
-Calculate each individual Employees daily hours as a general decimal number (example 7.5)
-Calculate Daily totals as a general decimal number
-Calculate total hours worked for week for each employee and display as a general decimal number


excel.jpg



The issues I am having is showing the total hours worked for an employee in a shift minus 30 minutes for lunch on shifts over 5.5 hours and displaying as a decimal number.

The formula i am using for displaying and showing the hours worked and subtracting the lunch break is

Code:
=IF(MAX(B4:B5)-MIN(B4:B5)<=TIME(5,30,0),MAX(B4:B5)-MIN(B4:B5),MAX(B4:B5)-MIN(B4:B5)-TIME(0,30,0))
This gives me the proper hours worked and subtracts for a shift over 5.5 hours but i am stuck displaying everything as a time.

I have the cell formatted as a hh.mm;@ so it looks alright but i am stuck on making a 7.3 display as a general decimal 7.5

I have tried formatting the cells as Numbers or even General and i am getting .3125 for a 7.5 hour shift.

I have a data validation list in my Start/Finish time cell's so i can choose from a list of start and finish times. I am not attached to them so if they need to go i am alright with that. They would be a nice addition though.


I would appreciate any help i could be offered to figuring this out. It is bugging the heck out of me.

Thanks very much in advance.

If you need any extra information, or access to the original sheet I can provide them.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
A quick piece of info, you say "i am getting .3125 for a 7.5 hour shift" ... that sounds like you are not doing any conversion on the time value ... to convert 7:30 to 7.5 ( General ), simply multiply by 24.
 
Upvote 0
A quick piece of info, you say "i am getting .3125 for a 7.5 hour shift" ... that sounds like you are not doing any conversion on the time value ... to convert 7:30 to 7.5 ( General ), simply multiply by 24.

Ya, i had that thought in the shower the other day and it seemed so stupid now that i think about it.

I worked yesterday so i did not have time to update this post with a note that i have found the solution.

The end result looks beautiful.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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