Problems displaying negative numbers for a flexitime spread sheet

[per11]

Hello all,

I have tired looking everywhere to look for a simple answer to what I thought was an easy question. Is it possible to enter a negative time without doing any calculations? I.E. if some has 5 hours flexitime on a time sheet for example and takes it off one day. And say they have a balance of 10 hours then the spread sheet will tell you that you have days left.

I have also been trying to work to work out if you can format a cell to either display a negative or positive time. Is this possible? For example if somebody has made a net loss on hours worked a month or a net gain of 11 hours.

Many thanks for your help, I hope I am not being silly.

Per

[shg]

Welcome to the board.

Please take a few minutes to read the Forum Rules about thread titles, and then amend yours accordingly.

Thanks.

[AliGW]

Sorry, but your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[bebo021999]

For example,

A1=10:00
B1=11:00
C1=B1-A1=1:00

A1=10:00
B1=08:00
C1=B1-A1=-02:00

Formula in C1:
=IF(B1<A1,"-"&TEXT(A1-B1,"hh:mm"),B1-A1)

Ofcourse, -02:00 is text itself and not be able taken to be calculated.

[shg]

In order to use negative times, you have to switch to the 1904 date system: Options > Advanced > When calculating this workbook, tick Use 1904 date system.

Be aware that it will change all dates entered as constants by 1462 days.

[per11]

This has been most helpful! Thank you very much.

Is it in order for me to share my finished flexi sheet with you all? It might be helpful to others.

[shg]

You're welcome.

Is it in order for me to share my finished flexi sheet with you all?

Certainly. That would be very kind.

[per11]

Ok here goes. It should be relatively straightforward. The only things an employee would need to fill in are the boxes that have a solid border. Everything else should be conditionally formatted with the correct equations.

There is one minor issue that you cannot add a negative time as the "carry over" time but there is a simple work around by placing a debit of the amount in the first day of work for a new calendar month. If there is an easy to resolve this it would be great but this is not essential. If there are any teething issues please do let me know and I can make the adjustments as necessary.

Thank you all for your help.

[shg]

You're welcome, good luck.

[lotusman]

Hi, Per,
A day is one unit in excel time, subdividing into hours & mins & seconds. But, whatever the reason, as you were saying yourself, it does not do negative time. I am on holidays now but, at work, I did something like this: say someone has a balance of 3:00, and they take a flexi-day, say 7:30, in effect, meaning they are now 4:30 down, you can have your formula say =abs(balance - hrs taken), which gives it a positive value (4:30). In another column there will be a display of "up" or "down" based on something like this: =if(hrs taken > balance,"down", "up) placed alongside the formulas with the absolute. So the 2 columns will now display "4:30 down". Can be done. THe other way would be two use ordinary numbers and be happy to display 7:30 as 7.5, or even 7 30/60... Hope these ideas might be helpful.

[per11]

Hi all,

I am sorry that this has taken so long to get back to you. I have finally finished my spread sheet and I have encountered many problems a long the way.

The attached version is the best one so far. I think I have chucked all the possible problems at it that I can think of but if there are errors please let me know and I will address them.

The way I overcame my problem was using the formula: =-NUMBER/(24*60) and this produces a negative number which works in conjunction with the rest of the spreadsheet. Thanks for all your help. I have updated your reputations!

PER11