#VALUE to 0 if there is no data?

Hi

I'm From The Netherlands

Unsolved

My New Question is on Page 2 plz Look ...................



Solved

I want to register every day working Hours
I got 2 things to input every day.

- Working Date
- Working Hours
1. How can i calculate The total working hours every week. (Monday to Sunday)
2. I would like to input the Working Date and Working Hours (using H8 and H10) and after i
press a button, it put the data to the Left side of Column A and B. Can someone help me plz.
I do not know howto programm (using the vba code)
3. Is it possible to Display the current date in H8, every time i open Excel
anwser by Forum Moderator Arlette : To display the current date, put this formula in H8 =today().

4. Could you explain how the code works? if needed i can modify in the future.
5. Could you make The latest Date and value on top and old below.
All new (Working Hours)date and value will be on A17 and B17
(Plz look at the attached picture) And for Lunchtime will be on R17 and S17

6. Unsolved I get a error message after i press the lunch Button
I dont understand why.. :-(

7. You said in Notes: I've added a named dynamic ranges. Which code do u mean?

8. Except there is a small little problem.
After i press the Lunch Button. I can see in Location O13.
The following:
A. #######
B. then it shows Total
- Is there a way to fix the #######

9. I need a formula .
How to display 3 different image based on if-condition?
Using one random value to show images.
Random number could looks like: 7 or 7,4 (seven point four)

If <=13 then show c5 which contain the image1
If >=14 and <=20 then show c6 which contain the image2
If >=21 then show c7 which contain the image3

10. Could you tell me how to change to zero:---------- > * *0

-----------------------------------------------


Thank You
Ray

---------------Picture ------------------------
working Hours v1.jpg

--------------- Excel Document ----------------
working Hours v1.xls

I think I cover your problem regarding point 1 & 2 by using dynamic PivotTable (Blad2).

Look at provided file. If you will need more assit just let me know.

Originally Posted by watersev

hi raygra, option to make it, please check attachment

Hi Watersev, Thank you for the Attached File. This is what Im looking for :-). thank you very much.

Still I have some more Questions:

4. Could you explain how the code works? if needed i can modify in the future.
5. Could you make The latest Date and value on top and old below.
All new (Working Hours)date and value will be on A17 and B17
(Plz look at the attached picture) And for Lunchtime will be on R17 and S17
6. I would like another button for lunchtime, almost same as working Hour
But this is on the Right side now(Plz look at the attached picture)

Originally Posted by maczaq

I think I cover your problem regarding point 1 & 2 by using dynamic PivotTable (Blad2).

Look at provided file. If you will need more assit just let me know.

Hi MaczaQ, Thank you for helping and providing the File. I must say It is close.
What i looking for. I think i gonna use the '"Watersev file'".
But thanks anyway ;-)

--------- Picture ----------------
Picture .jpg

4. The code with comments:

Please Login or Register  to view this content.

5. Done with a sort in code attached to the button
6. I think you can handle that part yourself basing on the ready sample for a Week case

please check attachment

Notes:
1. I've added named dynamic ranges for the formula to count correctly after new data entered, for the chart to reflect new date and hours and sorting of the data
2. I've added Validation for F8 and F10

4. Solved Thank you.
5. Solved Thank you.


First code--------
=SOMPRODUCT((A17:A100>=today()-(WEEKDAY(Today();2)-1))*(A17:A100<=Today()+(7-WEEKDAY(Today();2)))*B17:B100)

Second code -------
=SOMPRODUCT((WHdates>=today()-(WEEKDAY(Today();2)-1))*(WHdates<=Today()+(7-WEEKDAY(Today();2)));WHhour)


I use the old First Code, to control where to get the Value of Lunch to get the total

Originally Posted by watersev

Notes:
1. I've added named dynamic ranges for the formula to count correctly after new data entered, for the chart to reflect new date and hours and sorting of the data

6. Unsolved I get a error message after i press the lunch Button
I dont understand why.. :-(

7. You said in Notes: I've added a named dynamic ranges. Which code do u mean?


-------- Attachment Error File -----------
working_hours(1) (2) Error.xls

Thanks for the file. Everyting works just fine :-).

8. Except there is a small little problem.
After i press the Lunch Button. I can see in Location O13.
The following:

A. #######
B. then it shows Total

- Is there a way to fix the #######

Ray

----------- Picture ------------
1.jpg
----------- ------- ------------


I did try to move
Column A + B to C and D
Column Q + R to O and P

it worked but. Now the location O13 (O is letter not number) got a error (Did not get Total)
The Blue and green lines did not move.

----------------------

My Question is on Page 2 plz Look ...................

Hi

9. I need a formula .
How to display 3 different image based on if-condition?
Using one random value to show images.
Random number could looks like: 7 or 7,4 (seven point four)

If <=13 then show c5 which contain the image1
If >=14 and <=20 then show c6 which contain the image2
If >=21 then show c7 which contain the image3

Thank you
Ray

hi

I hash for disable this line

Please Login or Register  to view this content.

and then use this simple test for check what pictures you have loaded into workbook:

Please Login or Register  to view this content.

Finally I get results which shows that you haven't got picture 5 ;-) so you can not do it visable
results from test:
Picture 1
Picture 2
Picture 3
Picture 4

THere is a formula that calculate for this week only, if there is no value its shows: #VALUE!

10. Could you tell me how to change to zero:---------- > 0



-------------- ATTACHMENT ------------

Value v2.xls

-------------- ATTACHMENT ------------