please I need help with a time sheet

cosmy

New member
Joined
Oct 16, 2013
Messages
3
Reaction score
0
Points
0
Hi to everyone, I'm a new user and I'm trying to create a timesheet for my self to use for work, I've done the most of it but now i'm stuck at the end. When I have my days off and leave it blank instead of showing me 0 for working hours it show's ######## for the night shift and 18 for the day shift. Can anyone have a look and please tell me why, i want to mention that I don't have to many knowledge of excel in fact i start learning just to create this timesheet table.
Thank you
 

Attachments

  • Time sheet Calculator.xlsx
    12.2 KB · Views: 26

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Enter the following in E10
Code:
=(D10>0)*(($D$3-B10)+IF(C10<$D$4,0,C10-$D$4))
and pull down as needed

FWIW in the expression =sum(b5-c5) the sum part is redundant - =b5-c5 does the job

Concerning the #######, that is what XL answers when you get negative time or date difference. That is why you have used correctly =IF(B10>C10,C10+1-B10,C10-B10) which accounts for passing midnight.
You can eventually replace this expression by
Code:
= MOD(C10-B10,1)

You might also want to have a look at absolute and relative references. I suspect you entered all the formula manually without pulling down?
 

cosmy

New member
Joined
Oct 16, 2013
Messages
3
Reaction score
0
Points
0
TE=Pecoflyer;9725]Enter the following in E10
Code:
=(D10>0)*(($D$3-B10)+IF(C10<$D$4,0,C10-$D$4))
and pull down as needed

FWIW in the expression =sum(b5-c5) the sum part is redundant - =b5-c5 does the job

Concerning the #######, that is what XL answers when you get negative time or date difference. That is why you have used correctly =IF(B10>C10,C10+1-B10,C10-B10) which accounts for passing midnight.
You can eventually replace this expression by
Code:
= MOD(C10-B10,1)

You might also want to have a look at absolute and relative references. I suspect you entered all the formula manually without pulling down?[/QUOTE]

Thank you I'll try it as soon as I get home in the morning and yes you're right I've done it manually as I said I don't have to many knowledge about xl.
 

cosmy

New member
Joined
Oct 16, 2013
Messages
3
Reaction score
0
Points
0
Thank you, thank you very much, you made my day. I've done as you said and all are perfect now.
 
Top