Adding numbers in one cell

Topic

New Reply

Hello all…

I have a question regarding adding numbers in one cell. I am using Office 2007. The rows will contain customer information, the columns will have information such as order number, date, etc. What I want to do is have two cells to track hours billed. One cell we’ll call Hours Quoted. The other cell will be Actual Hours. In the Actual Hours cell I want to add hours worked each day and have the cell update the hours, then compare the total to the Quoted Hours cell, and if it has exceeded the Quoted hours, the numbers turn red. An example would be…..

Column C would be Quoted Hours…..10 hours
Column D would be Actual Hours……11 hours based on time added from two different days
Column D numbers are now red

Each day I would add the numbers of hours worked on a job into the cell in column D, add the hours would total up to show the number of hours worked so far. If the total hours in the cell under column D exceeds the number in the cell under column C, the column D cell numbers will turn red.

Anyone ever done this?

Thanks in advance for any help.

Reply | Quote

Replies

Try using a Conditional Format.

Assume Actual Hours are in cell D3
Click on Cell D3
Format/Conditional Formatting/Cell Value Is/greater than/then point to cell C3
in that same box , choose Format/Color/ and click on Red

Reply | Quote

Snapon,

Here’s a 3 sheet solution you may find useful. The sheets Summary, Jobs, & Hours will let you keep track of more data and make input easier.

On the Jobs Sheet you Enter Order#, Client Name, Quoted, and Actual. The last two get sumif formulas.

On the Hours sheet you enter the Date, Order#, Hours Worked one line per day per job, actually it could be multiple per day per Order# if you wish.

On the Summary sheet you enter The dates and the Clients names and 2 more sumif formulas.

What makes this all work is the dynamic named ranges which automatically adjust every time you add more data to the sheets.

Note the named ranges as entered only allow for 2000 rows but that is easily edited if necessary (Insert,Name,Define).

This may be more than you need at the moment but it was fun working it up anyway…enjoy!

RG

P.S. You could add conditional formatting to the Summary sheet to show if you work more than 8 hrs per day like on 7/1 in the example.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

How are the individual “Actual Hours” entered:

If you have one record row for each unique combination of Order Number and Date, yet work on a particular order in several dis-jointed session on any one Date, say from 8-10 (2hrs) then from 3-5:30 (2.5 hours), how does your spreadsheet accept those entries from you?
(1) Do you store a simple sum in the Actual Cell for that Order/Date combo, i.e. “=2+2.5” and manually edit the cell for another session that day, say after another 1.5hrs THAT SAME DAY you change the cell to “=2+2.5+1.5“?
(2) OR do you store each work SESSION on its own row with duplicate Order Number and Date for each session?

To my knowledge, only in case 1 will conditional format help you.

You could keep a normalized record of your work SESSIONS, one row per session, in one tab (say, SESSION), and your Order (Job?) definitions in another (say, JOB) along with the “Quoted Hours”. Then use a Pivot Table to do your summary.

You really have a database management application in mind, not a simple flat-file spreadsheet app. Excel can do astounding things, but IMHO it makes a lousy general word processor and a dangerous DBMS: using it for all but the simplest data management applications results in many opportunities for error and much repetitive entry.

I’ve seen some good Excel-based “TimeSheet” apps, but they are VB and/or macro heavy, performing functions that are routine to Access, Excel’s DBMS sibling.

I don’t know if I helped you, but please consider Access for this application.

Regards.

Reply | Quote

Hey Snapon2,
If I understand your question correctly, there are two things that you want to achieve, presumably without changing the layout of your current recording sheet too much…..

Firstly, you want to be able to enter each allocation of hours for a specific client into a cell (on a daily or otherwise basis), and have it automatically perform a calculation of the running total of hours, without having to perform that calculation yourself (and presumably not have to have numerous additional columns to hold that daily data, the number of which will vary dependent of the number of separate allocations of work to that client, and the time taken to complete the task?)

Secondly, based on that running sub total, you would like a conditional format to identify where this figure has exceeded the hrs quoted allotment

Assuming I have understood all that correctly, what you are asking for can be achieved, but with the addition of only one more column. I don’t know of any other way that excel can perform this task, without at least one more column, however there are many better skilled minds on this site than mine who may disagree and hopefully show me where I may be wrong.

As per the attached, the additional column will accept the hours worked for the day, add it to the running sub total figure, and then delete itself ready for the next allocation of time to that client. The reason for the additional column, is that I don’t believe that a cell is able to produce the result you are looking for (ie contain an existing value of A, have that value overriden by a daily value of B, and produce a result of A+B in that same cell without the calculation suggested by euHodos in point 1) above.

In saying all of this however, I can see that you may also be including an inherent flaw into the methodology, in that once the hours are entered, there is no way to identify whether you did indeed update them or not (ie it’s possible to forget for example). To that end, I created another column which will automatically enter the time (and date if you want, however I formatted the cell just to show the time only) that the last change was made. This can be deleted each day (which is why it is formatted to capture time only), or can be left in perpetuity and reformatted to show the date and time. It can, of course, be removed altogether from the code should you not want that functionality to be applied. Be aware though, that should you want it to remain, your existing data capture sheet will need to have another blank column inserted as column F, so that it doesn’t overwrite information that you may already have in that column.

I hope I’ve understood your problem, and I hope this helps

Reply | Quote

You can do a running total in one cell by using the comment section of that cell then do a conditional format to turn the cell red if it exceeds the quoted hours. If you want to “clear” the cell though you will have to either edit the comment or simply subtract the number that is in the cell i.e. cell = 32 then -32 will return zero. Right clicking and choosing “clear contents” will clear the cell but will not reset the comments to zero. A sample sheet with the macro is attached.

Reply | Quote