calculation time in schedule with IF

[taken]

Hey guys!

I'm quite new to Excel but I'm getting there I think.
I've got this example where I have my schedule at work for my employees. I got fed up calculating everything in my head so I started to create a schedule that makes it for me.

There's no problems to make an IF-statement for lets say Monday.
In this case i take two cells. I compare them and the result is the time in hours for that day.
The statement tells me if the person has been working more or less then five hours. If the person has been working more, then it takes 30 minutes of the result for lunch.

But, my problems comes when I need to set statements for each and everyday in the week.
How in the world can I tell every days times the same thing and still get the total hours worked per week, without a big FALSE.

I can send a file to anyone who can help me. Really need to sort this out.

Thank you!

// Martin

 

[GRTorres]

Doesn't seem entirely to difficult, but I think we can better serve you if we had an example of what you want it to exactly look like. Thanks.

 

[taken]

Doesn't seem entirely to difficult, but I think we can better serve you if we had an example of what you want it to exactly look like. Thanks.

Absolutely.. Is there any way to upload files here or should I use my own server?

 

[T. Valko]

Absolutely.. Is there any way to upload files here or should I use my own server?

You can't upload files to this site.

If you have your own server then you can put the file on it and post a link to the file.

 

[taken]

You can't upload files to this site.

If you have your own server then you can put the file on it and post a link to the file.

You can download the file at:

http://www.martinhult.se/test.xlsx

Thank you!

 

[taken]

The problem is solved after some trial and errors.
I just made a calculation of the time underneath each day then added all these together for a result with if-statements.
Then I just hid the row so it's not visible anymore. =)

 

[T. Valko]

The problem is solved after some trial and errors.
I just made a calculation of the time underneath each day then added all these together for a result with if-statements.
Then I just hid the row so it's not visible anymore. =)

I looked at your file...

You do need a separate cell for this calculation.

I used this formula:

B4 = Emma
C4 = 9;15
D4 = 20:15

=D4-C4-((D4-C4>=5/24)*30/1440)

Format as h:mm

Then you can just use a SUM(...) function on these results.

 

[taken]

yeah .. i did just like that.
So, you can download http://www.martinhult.se/schema_mall2013.xlsx
There's an update of what I've done so far.

My problem right now comes to macro.
I want to be able to make a row when clicking one of the buttons at the top.
I can get the row. But, I just don't get it working properly.
Also .. is there any kind of toggle funktion so I can toggle hide and unhide specific rows?
I want the user to be able to hide or unhide the hour-cells underneath the times.

 

[taken]

yeah .. i did just like that.
So, you can download http://www.martinhult.se/schema_mall2013.xlsx
There's an update of what I've done so far.

My problem right now comes to macro.
I want to be able to make a row when clicking one of the buttons at the top.
I can get the row. But, I just don't get it working properly.
Also .. is there any kind of toggle funktion so I can toggle hide and unhide specific rows?
I want the user to be able to hide or unhide the hour-cells underneath the times.

Also .. will the created row from the macro also effect it with the same settings as the other ones?

 

[T. Valko]

yeah .. i did just like that.
So, you can download http://www.martinhult.se/schema_mall2013.xlsx
There's an update of what I've done so far.

My problem right now comes to macro.
I want to be able to make a row when clicking one of the buttons at the top.
I can get the row. But, I just don't get it working properly.
Also .. is there any kind of toggle funktion so I can toggle hide and unhide specific rows?
I want the user to be able to hide or unhide the hour-cells underneath the times.

Unfortunately, I'm not much of a programmer so someone else will need to help you with that.

If you don't get any more replies in this thread (after a reasonable amount of time) then start a new thread and make sure you put macro or VBA in the thread subject line.