Timesheets and Minus Time

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I've been plugging away at a self adding timesheet all morning, and it's driving me insane. :o


    It's on a 24 hr clock, and it needs to let people carry over their hours below or in excess of 35 to the following pay period.


    The minus hours are cauing the problem, so I found a little bit of code in a reply to somebody elses problem which might help:


    =IF(A1>B1,"-"&TEXT(B1+1-A1,"h:mm"),B1-A1)


    Please can you explain what each bit of this means so that I can apply it to my own problem?


    Oh, and can I use a figure that comes out of this as a basis for another time based calculation?

    In
    =IF(A1>B1,"-"&TEXT(B1+1-A1,"h:mm"),B1-A1)


    If A1 is > B1 then B!+1-A1 will be calculated and written as a text string using the H:mm format, otherwise the value of B1-A1 will be returned.


    As to your problem with minus hours, could you be more explicit or post an example of what you are trying to do? One secret of working with time, is that Excel keeps track of time as a fraction of the day. If you are using time formats, then arithmetic operations need to take that into account. If cell A1 has a time and you wantto subtract one hour from it, subtract 1/24 instead.

    Oh dear - Thanks for trying, but I'm not sure I understand the explanation either :rolleyes:


    An example sheet is attached with the problem areas highlighted.


    All of our staff are contracted to work 35 hours per week on a flexi basis.


    For example if they work 36 hours one week then they are 1 hour in credit, and 1 hour will be taken away from the hours they need to work the following week.


    If they work 34 hours one week then they are 1 hour in debt, and an hour will be added to the hours they need to work the following week.


    Does that help at all? Thank you so much for this - it's such a relief to get some help on it!

    See the attached, which makes use of the 24 hours in a day and the TEXT function. I do not understand, though, why your second case was supposed to read 35:45. It seemed that it should be 1:00 as the formula calculates.

    Wow! Thank you so much! It's so close now! :biggrin: :spin:


    The second one should have read 35:45 because although the person had worked 36 hours that week, they had worked under their contracted hours by 15 minutes the previous week, they owed 15 minutes.


    Will that work with those bits of (very long and impressive!) code please?

    I see. Then you will need to have for each person a calculated field that draws upon the previous week to see how many hours they are supposed to work this week. That cell reference can take the place of the 35 in the formula. The whole process would be simpler if you converted the time values in column H to number of hours in column J without using text values or time formats. So 6:15 would translate 6.25. Then simple arithmetic would accomplish what the formulas are doing and make carryovers easier.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login