need help with my formula

A

arne

New member
Joined
Aug 17, 2014
Messages
3
Reaction score
0
Points
0
hi guys don't know if I posted in the correct place I need help with my formula its for a time sheet if I enter in 16:00 and out 01:00 it shows me -15 hours where it should be 7 and then 2 hours ovetime, please I tried everything and cant fix this
 

Attachments

  • new time sheet.xlsx
    11.7 KB · Views: 15
Hello
The problem is that your only showing the time in your "in" and "out" cols. This works OK until the two times are in different days. Change the content in these cells to include the date as well, and you will get the correct calculation.
You can still format the cells to display the time only if you wish.
 
this did not work my hours can only be 7 any hours more then 7 should go to the ovetime tab
 
Without changing anything you already have on your worksheet, try this and drag down

G5 =IF(ABS(($F5)-($C5)+(($C5)>($F5)))*24<=7,ABS(($F5)-($C5)+(($C5)>($F5)))*24,7)

H5 =IF(ABS(($F5)-($C5)+(($C5)>($F5)))*24>7,(ABS(($F5)-($C5)+(($C5)>($F5)))*24)-7,0)
 
arne said:
this did not work my hours can only be 7 any hours more then 7 should go to the ovetime tab
Click to expand...

I know that you have a solution now, but I wanted to show that my suggestion also works. If you look at the cells Ive coloured red and green, the red does not have a fully specified date and time producing the erroneous difference of -15.
The green has the fully specified date and time, and shows the correct figure of 7 with 2 hours overtime.
 

Attachments

  • new time sheet1.xlsx
    12.9 KB · Views: 8
You must log in or register to reply here.
Back
Top