• 100% Satisfaction Guarantee
Kamil Anwar, Office Specialist
Category: Microsoft Office
Satisfied Customers: 5124
Experience:  8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
69350257
Type Your Microsoft Office Question Here...
Kamil Anwar is online now

# Could you please assist in a formula to calculate hours in

Could you please assist in a formula to calculate hours in a day and breaking down to an individual activity? For example in one column I will have the activity e.g. auditing, safety walks etc. In another column I will have the hours spent on that activity. I need a formula which can calculate the hours by reading and placing with that activity so I can compile total hours to that activity for a week or year etc.

Kamil Anwar :

Hello & Welcome to JustAnswer. My name is XXXXX XXXXX i will do my best to assist you with this question.

Kamil Anwar :

I will need to take a look at the document to write a formula to fit your requirements.

Customer:

Were do I send

Kamil Anwar :

Kamil Anwar :

I cannot download this can you copy the other link shown there or the file id shown.

Customer:

Kamil Anwar :

Thanks - Please give me a few minutes to check this.

Customer:

You will see in Cell B44 my attempt to write a formula

Kamil Anwar :

I am reconfiguring my office, please give me a moment.

Kamil Anwar :

I am extremely sorry for the wait but i am having problems with the Office. I am trying to fix it, will take a few minutes.

Customer:

Ok

Kamil Anwar :

=SUMPRODUCT((D2:D6="Drills / Exercises")*(C2:C6=""))

Kamil Anwar :

I am in the file now.

Kamil Anwar :

The Activity is in Column A

Kamil Anwar :

The Date is in Column B

Kamil Anwar :

The Hours are in Column C

Customer:

Activity is under Cells marked in red titled Task D,G,H etc I need the formula to identify the activity and place the hours accumilated so I can build a charts to show the figures for weekly and monthly reports

Kamil Anwar :

Is there a condition on which basis to identify the activity?

Customer:

I don't believe I will need to capture the dates

Customer:

Not sure what you mean by condition

Kamil Anwar :

You stated you need a formula to identify the activity and place the hours accumulated so you build a weekly chart.

Customer:

Basically if the formula reads auditing and then 3 hours I need the 3 hours to go to auditing and to calculate as I move across the spread sheet in which I can build an accumulative total over a set period of time.

Kamil Anwar :

If we say

2 hours spent on activity A
2 hours spent on activity B
3 hours spent on activity D

In a day.

Kamil Anwar :

Do you want the total for each activity (for that day to go under the listed activities at the bottom of the sheet)?

Customer:

When you reference A,B,& D are refering to Cells or an example?

Kamil Anwar :

Just an example

Kamil Anwar :

So i can understand it more clearly

Customer:

Yes that what I would be looking for so those hours are broken down when the formula identifies activity and puts the hours with it to create a total over a period of time.

Kamil Anwar :

What would that period of time be (week) ?

Customer:

A week would be good but I probably will end up going into a month. If we do it as a week I can make a formula to accumalate over the month

Kamil Anwar :

Okay. Let me try something.

Kamil Anwar :

What is column A for?

Customer:

They are for break down for each work site. I was going to repeat the formula for each work site to read the required cells in there areas. This is unless your formula will pick up work sites as well?

Kamil Anwar :

Thanks for the information. I am working on it to get the output now

Kamil Anwar :

Test.xlsx

Kamil Anwar :

I have simplified it so that we can prepare a correct template.

Customer:

A heap of folder came up when I down loaded. Did you send the spread sheet back?

Kamil Anwar :

Yes

Kamil Anwar :

It is called test.xlsx

Kamil Anwar :

Customer:

Comes up with folders

Kamil Anwar :

Whats the name of the folder?

Customer:

Cannot find tests

Customer:

_rels, docProps,xl

Kamil Anwar :

Is it after you click test.xlsx?

Customer:

Kamil Anwar :

Strange, it's working here. Let me upload it on a different site.

Customer:

you can send [email protected]

Kamil Anwar :

I can't see your email as it's filtered. It is against the site policy to contact on e-mail, unfortunately.

Customer:

There may be filters as I'm on a work server, not sure

Kamil Anwar :

Kamil Anwar :

I guess that's the issue then which is why you are not able to view the file.

Customer:

When I click on test it just highlights, so i then hit download and it gives me the folders mentioned

Kamil Anwar :

Let me add the file to zip

Kamil Anwar :

Customer:

Access is blocked for this site

Kamil Anwar :

Are you trying to open the file using Excel?

Kamil Anwar :

Can you open Excel first and then try and open the test.xlsx file from within the Excel.

Customer:

I'm getting blocked before getting a chance to open. the only one I have been able to open is wiki

Kamil Anwar :

Kamil Anwar :

When you open the wiki site.

Kamil Anwar :

opened*

Kamil Anwar :
Kamil Anwar :

Can you click the Full Size Image to see if it downloads the file?

Customer:

I have opened and looking now

Kamil Anwar :

Great

Customer:

Yes that is a bit to basic, I will need to spread the hours over the dates not as a day 1,2,3 ect. I will be adding comments under the dates.

Kamil Anwar :

We can change the days with the dates - That is just to clarify the requirements.

Kamil Anwar :

You will input the hours under a date.

Customer:

You will see that I have place the activities under drop boxes this so when filling in daily comments I don't have the full list of activities listed to reduce the page size. If we could get the formula to regognise the activity and then add those hours to it which I will do under hiden cells so I can develop charts

Kamil Anwar :

That means, Formula should first identify the task, then get the hour and the sum it up (to get a weekly total)

Customer:

yes

Kamil Anwar :

Since both the columns C & D are unrelated - It will not be possible to achieve this with limited excel functionality. There has to be a bridge or reference to a column with the value in order to get this done.

Kamil Anwar :

How many tasks/activities are there in total?

Kamil Anwar :

Also logically, the drop down gives you the ability to select same many time which can conflict.

Customer:

=SUMIF(D2:D6,"=Drills / Exercises",C2:C6). Used this formula which seems to work to acheive the information needed.

Kamil Anwar :

This brings the total for each day only, based on this we can try and get weekly / monthly totals.

Kamil Anwar :

=SUMIF(D2:D6,"=Drills / Exercises",C2:C6)+SUMIF(J2:J6,"=Drills / Exercises",I2:I6)

Customer:

=SUMIF(D2:FB6,"=Auditing",C2:C6). I put this in which calculates to the end of next month. I can change the first part to suit time frames.

Kamil Anwar :

I guess you've already found the solution.

Customer:

I think this is what I'm looking for. I'm putting the formula in now to run a test

Kamil Anwar :

Great. I am also looking for more options.

Customer:

Cheers

Kamil Anwar :

It seems like you have gone offline. Kindly let me know the results. Thanks

Kamil Anwar :

Are you still there?

Customer:

Working well

Kamil Anwar : Excellent. I have unlocked the rating panel so you can rate me. If you need further assistance please let me know.