Results 1 to 4 of 4

Thread: please I need help with a time sheet

  1. #1

    please I need help with a time sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,504
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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?

  3. #3
    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.

  4. #4
    Thank you, thank you very much, you made my day. I've done as you said and all are perfect now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •