# Schedule Worksheet, Issue with displaying Hours worked as general number

#### NeoStylez

##### New Member
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

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.

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

#### GlennUK

##### Well-known Member
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.

#### NeoStylez

##### New Member
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.