LinkBack URL
About LinkBacks
The first formula
=IF(B7="na",0,((C7-B7)*24))
I need to add
=IF(C7-B7)*24>6,-0.5
to get something like this
=IF(B7="na",0, ((C7-B7)*24))>6 -.5 otherwise (C7-B7)*24))
The first formula
=IF(B7="na",0,((C7-B7)*24))
I need to add
=IF(C7-B7)*24>6,-0.5
to get something like this
=IF(B7="na",0, ((C7-B7)*24))>6 -.5 otherwise (C7-B7)*24))
Try:
=IF(((C7-B7)*24)>6,-0.5,IF(B7="na",0,(C7-B7)*24))
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thanks for your help, unfortunately it didn't work. The result is -.50
The formula calculates the hrs between 7 am and 4 pm =9 hrs, as this number is greater than 6 hrs I need it to subtract.5 hrs. Thus giving me 8.5
Now it is giving me #Value! in the cells containing na
here is a image of the workbook Example.png
Can you upload a small sample workbook? Not a picture, pls?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Can't see where the insert workbook button is
Is giving you error as you are trying to make calcualtion on Text(na) and empty cell.
Try:
=IFERROR(IF(((C7-B7)*24)>6,((C7-B7)*24)-0.5,IF(B7="na",0,(C7-B7)*24)),"")
Cheers dude, formula works, only one problem now when I do auto sum for total hours for week #Value appears, is this due to the text in the new fromula as it wasn't a problem beforeOriginally Posted by RobertMika
Change "" to 0
=IFERROR(IF(((C7-B7)*24)>6,((C7-B7)*24)-0.5,IF(B7="na",0,(C7-B7)*24)),0)
Please add reputation.
Thanks Rob Reputation has been left, thank you for your assistance.
You are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks