Schedule Worksheet, Issue with displaying Hours worked as general number

[NeoStylez]

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

=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.

 

[GlennUK]

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]

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.