Email:      Pass:    Pass?

Free Excel Forum

How To Get Rid Of #value In A Cell

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi there,

Please see attachment. The formula I have in cell P19 give the correct figure when the hours worked are inserted, but how do I get rid of #VALUE? I would like the cell to remain clear.


Similar Excel Video Tutorials

Helpful Excel Macros

Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics


Basically I am working on a timesheet where the person has basic contracted hrs of work.{17:30-20:45) Now if he works outside of those hrs he receives overtime {M20}. If he starts work after 17:30 he will still get paid from 17:30.

I have put together a formula but I don't know if it is correct, in fact I think it's wrong {although it still works} it is not giving me exactly what I want.
I would like a formula lets use cell P20, that calculates hrs worked before 20:00hrs bearing in mind that if the start time is after 17:30 they will still be paid from 17:30, but must also include start times before 17:30. {I created a "hidden" column {G} with 20:00 in the cell as I could not figure out any other way to do it. But then 20:00 is automatically in cell P20 until hrs worked are inserted then the figure changes, where I would prefer it to be blank to begin with. Is there a simple way of doing it?

I would also like the figure to be in a number format instead of, so 2.5 instead of 2:30. In my formula I have had to multiply 2:30 by 24 to get to the correct total amount.

I have attached a sample of the sheet I am working on...

I have had no training in formulae so I am trying to use logic and work them out myself.. to my own detriment I reckon. But at least I am trying.



Basically I am working on a timesheet where the person has basic contracted hrs of work.{17:30-20:45) Now if he works outside of those hrs he receives overtime {M7}. If he starts work after 17:30 he will still get paid from 17:30. The formula was kindly worked out for me but the figure in M7 (overtime) show ##### before the hours worked are imputted.

Can I get rid of the #.. so that the formula remains but the cell is blank or at least shows zero?

I have attached a sample of the sheet I am working on... There are other errors but I think I need to create a new thread for those!

I have had no training in formulae so I am trying to use logic and work them out myself.. to my own detriment I reckon. But at least I am trying.


So I keep a spreadsheet to track eligibility for insurance benefits.

In order to be eligible, an employee has to have worked 12 consecutive months at 110 or more hours per month. If they work less than 110 hours any month, the count starts over.

I need a formula that will tell me when they are eligible for benefits.

On my spreadsheet:

Employee 1 worked 110 hours or more for 12 consecutive months (06/09 - 05/10) He would be eligible on 05/10. In cell H2, I would like it to display the date Employee 1 hit the eligibility (The Heading date), 05/10.

Employee 2 didn't start working until 03/10. He worked 110 hours or more for 12 consecutive months (03/10 - 02/11). I want cell H3 to display 02/11.

Employee 3 worked 06/09 - 10/09 at 110 hours or more, BUT he only worked 89.5 hours in 11/09, which means his count would start over, he then worked 12/09 - 11/10 at 110 hours or more which would make him eligible 11/10.

Employee 4 started working 07/10 and has only worked 11 consecutive months at 110 hours or more, cell H5 should remain blank until he works one more month at 110 hours or more.

Employee 5 has worked every month, some months at 110 hours or more, some months less than 110. He has not worked 12 consecutive months at 110 hours or more so cell H6 should remain blank.

I add columns to the end each month with their hours.

I tried my best explaining, please let me know if you have any questions.

I would be SOOOO grateful if someone could assist me with a formula, it's really hard tracking eligibility for so many employees manually!

Thank you!


Hi Guys,

I need a formula that will Calculate days and hours worked but need to take certain criterias into account. Say i have dates and time worked and finished liked this

22/03/2011 08:30 22/03/2011 16:30
23/03/2011 08:00

23/03/2011 17:00

24/03/2011 08:30

24/03/2011 17:30

Normal working hours are between 08:30 - 16:30 plus 30 mins lunch if in a day you have worked more than 6 hours. If you worked less than 6 hours then you have no lunch.
1 day is 7.5 hours

What i need to take into account is exclude weekends. Take the lunch criteria above into account. In 1 cell give me the days and hours worked between the normal working hours and in another cell give me the days and hours worked outside the rostered hours.

Hello, I am trying to simplify a works rota. What I want to do is a final calculation of a single person's hours.To do this I want to check a column with the different workers names in it. If for e.g. Monday 1st has "bob" working, then i want to add the figure in the hours worked colum together to give a sum total for the month. The final monthly hours sum will check for every occurence of "bob" and add the corresponding hours worked.

So if "bob" is in a cell, take the number in the adjacent cell (e.g. 8 hours) and add it to every occurrence of "Bob" for the month.
1 Hours Person
2 8 Tom
3 6 Bob
4 6 Ray
5 8 Bob
6 9 Mike
7 8 bob

Worker Bob hours worked = 22
if CELL F50(for e.g.)="bob" then check column B for "bob" and add every corresponding cell from column A to give the answer in CELL F51(for e.g.)
This being a first time post I hope i have explained well enough.

Thanks for any replies.

I am trying to formulate a formula that will calculate overtime hours worked.
Now standard hours are 17:30pm - 20:45pm. Anything outside these hours are overtime. If the start time is 18:00pm then the person is still paid from 17:30pm @ standard rate regardless.

Now I am trying to work out a formula that will cover hrs outside of the standard hrs AND hrs unworked but paid for.

Pls see attached! September tab {blue highlighted cells}

If anyone can advise I would be truly grateful, as I have spent hours trying to figure it out.

Kind Regards

Hey all,

I was wondering if there was a formula to figure out how many hours are worked and get the result as an integer.

I want to have two cells, one start time, ex: 2/14/2009 11:00:00 AM, one end time, ex: 2/14/2009 8:15:00 PM, and have the third cell give me the hours worked in an integer form - ex for this ex: 9.25.

Thoughts on the formula or if its possible?

Many thanks,


This seems like it would be fairly easy and I can't seem to figure it out! I am looking for a formula that will subtract 30 minutes for every 8.5 hours worked. The spreadsheet I am working with, given to me by the accounting department, only allows for total hours worked. They do not want/need a break down of each day.

I am using the networkdays formula to calculate the hours worked, right now it has a result of 17 hours, but it needs to be 16.

I have attached a copy of the worksheet I am using. The result of the formula will be in cell G33.

Thanks for your help!

If anyone can help me with this it would be greatly appreciated. I am using the following formula to determine if an employee has worked their required hours for the month to show "Nil", if they have worked less than one extra day (7hrs) "Hours", or if they have worked more than an extra 7 hours "Days". It is working fine except that I have one person that has worked their exact hours but it shows "Days" instead of Nil. I figure this is because it is set up in custom hours [h]:mm but I can't work out how to correct this. Have tried entering 0:00 in my formula.


I used this Worksheet for a different type of job.

for a mix work normaly I write any description, which it calculate the total worked hours subtracted REST etc.

But I have a different value paid when I do CAD work; of course I have to specify the time worked.
I have to have just only Cad work hours but not in both (T. Hours and Cad Work) columns If description is written "Cad work"

Like... =IF(C6="Cad Work";G6;0)

Is there a formula to clear a cell if any expecific test is writen like this sampel?
G10 is clear and J10 show the hours worked Cad Work .


Hope this is the correct forum to use!

I have a sheet that already has the total hours worked out (A1=date; B1=Day; C1=Start time; D1=End Time; E1=Total Hours; F1=Lunch 1/2 hour; G1=Total hours worked in that day). I now need a IF formula that can work out, per day, any hours above the regular 8 hours and any hours below the regular 8 hours and then at the end of the column SUM all the row values to give a total of what the actual overtime would be.

my excell spreadsheet looks like this
colum A 20 peoples names
colum B-H days of week
colum I will calculate total hours worked for employees
with a grand total at the bottom of I
need to calculate hours worked totals without 30 min lunch breaks if hours
worked is over 7.5 hours

so cell B2 would say 9-5:30
need cell B3 to say 8 (as in 8 hours worked with 30 min break)

but part of the problem is that a person can work 7 hours without a lunch
and get paid so we cant take the 30 min break out unless empleyee is over 7.5

the real answer I need in the end is total hours without breaks for full
time and part time people just cant figure out the formula that eliminates
that half hour and a formula that reads 2 times in 1 cell (9-5:30) if I have
to break that part into 2 cells be it... IF ANYONE CAN HELP that would
be great ....been trying to work on it for 3 days

Hello All,

I am trying to create an Excel spreadsheet for my partner to work out her hours worked. However, for some reason unbeknown to me they pay her for incomplete hours worked in a decimal value. Complete hours are paid on an hourly rate as per normal.

The TOTAL HOURS are easily worked out using Excel however; I am having trouble trying to work out the MINUTE value into a decimal value.













It is worked as follows using the values in the table above:

Whole hours remain as gained from (TOTAL HOURS) + (.40/60 x 100) = 9 + 0.67 = 9.67

I am not even sure if it is even possible to do this in Excel?

I.E. splitting one value (TOTAL HOURS) into two different values (HOURS & MINUTES) and treating each value differently (whole hours remain untouched and the minutes are manipulated as explained above) then bringing them back together for a final value (DECIMAL VALUE)?

Any ideas or directions to solve this would be gratefully received.



Hi there. I have another formula I would like help with (please) using the IF function. Well i think its the IF function.....

Please see attachment.

The total number of hours worked per day in cell C17. In this case its 16 hours.

The first 8 hours are paid at Normal Rate, 8.01 to 9.99 hours are paid at 1.5x normal rate and.... 10 hours + are paid at double time.
I would like to in cell C19 C19 and C20 have number of hours automatically put into these cells, as with each day of the week.

I hope this is enough detail for you.

Thanking you in advance.....


See attachment below....I forgot to attach it....sorry

Ok its me again...
This is what I am trying to do...
Column F is the number of hours worked per day
Column G is the Cumulative hours worked for the week
I want Column H to figure out how many Over Time Hours (hours >40) for that day. So this is my thought process... If G13 is greater than 40, then take G13 and subtract 40, and subtract any overtime from previous days so I get the over time worked per day. BUT if NO over time is worked I want nothing to appear in column H...

This is the formula I am using... that is NOT working


Can any one tell me whats wrong with this formula or give me a better one????

Any help would be greatly appreciated...

Thanks so much

I have created a table to calculate the total number of hours a person has worked in a week. My problem is that if a person works from 7:00AM to 5:00PM (minus a 30 minute lunch) the spread sheet shows the correct amount of hours worked which is 9:30. My problem is getting the total hours worked to calculate overtime in the pay cell.

So lets say I have G11 totaling 46:00 (or 46 hours). In my payment cell (h11) I want that to be able to calculate the rate of pay in cell B1 and then calculate the Overtime pay of 6 hours plus the regular 40 hours from Cell G11. I've attached a jpg to help clarify things.


I am trying to find out how to do some basic calculations using time ( i.e. hh:mm:ss).
I have a sheet which shows the number of hours spent on a task each day which I need to add up to get a total for a month in hh:mm:ss format. I have found you can do this by setting the format of the cell where you add up the totals to [h]:mm:ss format. This works quite happily and gives you a total hours worked on that task for the month.

What I now want to do though is display this as a % of the total hours in the month spent on all tasks. So say you worked X days this month and you know each day was 9 hours long. X is a variable number say in Cell A1. You would have a formula to work out the total worked hours (9*A1) . However I can't quite find a way to calcualte this correctly in [h]:mm:ss equivalent format. I need to then divide the total hours worked on my task by this figure and display as a %. i.e what % of the total hours worked this month were spent on this task.

I am really struggling to work out a formula that can calculate the total hours for X days in the equivalent of [h]:mm:ss format and produce a correct result as a percentage.

Any bright sparks out there know how to do this?

Many Thanks


I have spent way too many hours trying to find the correct formula to calculate total hours worked from start time and finish time, to give up now? What is the correct function needed which will also allow the sum of the total hours per day worked be totalled at the end?? Does that make sense? I'm losing it here...

I was going to attach it but apparently xls is not a valid file format in this forum???? im going to cry now, promise

Hello again all,

I'm hoping one of you fine folks can lend me a hand again. I'm trying to figure the formula to use for the following scenario. i'll try to explain it so that it doesn't confuse ya too much (or myself for that matter)

A1 = RATE 1
A2 = RATE 2

B2 = DIFFERENTIAL (this is essentially a makeshift "check box" where if "x" is entered, it means this person has a different rate for that day.

Now what I'm trying to get is...

C1 = Code:

 if B1="0" then "0.00" or if B1>"0" then B1*A1  or if B1>"0" AND B2="x" OR B2="X" then B1*A2

in other words,

if zero hours are worked(B1), then output "0.00"
if more then zero hours are worked, then hours worked(B2) times Rate 1 (A1)
if more then zero hours are worked and there is an "x" or "X" in B2, then hours worked times rate 2 (A2)

Thanks for any help ya can offer

I have numbers in a col like the following:


denotes 6hrs , 45min and 0 sec of hours worked.

In another cell i want to say if hours worked is over 8 then put an 8.

In another cell i want to put number of hours that are over 8. If hours worked are less than 8 put 0.

can some one help?

I'm creating a timesheet that uses 10ths of hours and I would like to be able to figure out a way that when a user enters time in the "Non-Worked Hours" columns all they have to do is single left-click a yellow cell for the appropriate time used and it will autofill the difference in 10ths of hours from a formula used.

Also, if someone could double check the formula I have used for accuracy I would appreciate it. =((CELL2-CELL1)*1440)/60

I would also like to try to figure out how to make the cells in Column G "Hours Worked per Entry", that has the above formula, appear blank until Columns D and F are filled in.

This is the spreadsheet:

Thanks for any help I really appreciate everyones input on this forum.

Good day,

I am having some struggles trying to calculate a difference in hours/minutes. So far I have a spreadsheet that is broken into 7 day and each day I am recording hours/minutes worked. At the far right of the spreadsheet I am tabulating the total # of hours/minutes worked. It seems to be working as needed but I would like to also calculate the wage based on hours worked.

For example:
- Y5 equals the total hours/minutes for the week
- I would like Z5 to be an "IF" statement that will show 40 hours worked if Y5 is over 40, or if it is less than 40, just the total hours worked in Y5.
- I would then like AA to show the amount of hours/minutes above 40 hours from the total shown in Y5. If Y5 is below 40 obviously I would like the cell to equal 0. I know these are both "IF" statements however I cannot figure out how to make them work.

Can anyone provide some assistance?



I have hours worked for employees listed over a monthly period. In the row below I have listed if they have worked their set number of hours for the month, more hours or less hours.

e.g one person has worked 420 hrs, which is their correct number of hours. The second person has worked 462 but their standard hours are 456, and the third person has worked 444 hrs but their set hours are 456.

Can someone please tell me what formula I can use to change the 6 hrs over, 12 hours under etc into days, based on the standard length of each day worked being 7 hours.


HI im not sure if this is possible, is there a way to enter/change the total hours into a split of 4?

For example If John Bloggs worked betweeen 09:00 to 17:00 with 45mins break for lunch, it means he worked a total of 7 hours and 15 mins.

I have a formula to calculate the hours wroked minus the breaks, however the result shows as 07:15, this is due to the hours being formated in hh:mm

Is there a way to have another cell show 7.25 for the 07:15 hours worked? At the moment I am manually entering 7.25 in to the cell, just wanted to know if there was an easier way.


Thank you in advance for your help with this, I have been spinning my head trying to figure out the formula for the data query I am trying to do. It's a bit hard to explain but here it is. I have a table similar to the following:

Name Date Hours-Worked
John 7/1/2008 3
John 7/2/2008 5
John 7/3/2008 8
Mary 7/1/2008 2
Mary 7/2/2008 4
Mary 7/3/2008 7
Tom 7/1/2008 9
Tom 7/2/2008 3
..... .... ..
.... .... ..

I need a formula that will look up a person's hours worked on a specific date. So for example I want to know how many hours Mary worked on July 3rd, 2008. The answer is 7. How can I write a formula for this? I tried vlookup, index, match, etc but I ended up confusing myself and not being able to link the first two columns together to give me the correct answer in the third.

Please help! And thank you kindly.