counting in 7.5hr blocks 1

[cantfindausername]

I am creating a down time work sheet for a test report.

So far I have a column for each day, and rows for 15 blocks between 0800 and 1900.

My current calculation works by entering the number of testers affected per 15 minutes. So if you put 4 in one cell it will clock up 1 hr.

However, what I need now is to clock up days (man days) as the count reaches over 7.5 hrs.

So if I have 10 hrs down time, it will be 1 day 2:30 hrs.

Does anyone know how I could do this please?

Many thanks,
Ant

 

[TonyJollans]

Hi Ant,

Can you give a few more details ..

Sounds like you are summing a column and multiplying by (some value representing 15 minutes).

Can you not change it to summing the same column and multiplying by (some value representing 1/30 of a man day)?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[cantfindausername]

Thanks Tony.

I've tried that and its bringing me back to the same thing.

I can calculate the time correctly, but what I'm ultimately trying to acheive is to make 7.5hrs = 1 day, so that for every 7.5hrs that are calculated, it ticks over 1 day.

Thanks,
Ant

 

[GlennUK]

So, you are getting a total number of hours, and just want to know the integer divide result, when dividing by 7.5 hours? If that's it, then add-in the Analysis Tool-Pak, and use a formula of:
=QUOTIENT(A1,"07:30:00")

changing A1 to whatever cell reference your total time is in.

To get the remainder not included in whole days, you could use:
=MOD(A1,"07:30:00")


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

Why be so complicated - just [tt]A1/"07:30:00"[/tt] formated as a number should do.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[cantfindausername]

Thanks guys, I'll give both a go and let you know what happens.

Cheers,
Ant

 

[GlennUK]

Hi Tony, that'll give the man-days in decimal, but it was requested that results be like this:

So if I have 10 hrs down time, it will be 1 day 2:30 hrs

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cantfindausername]

Glenn, your solution worked a treat.

Thanks to both of you for your help and suggestions on this. Its much appreciated!!

Ant

 

[TonyJollans]

Oops! Quite right, Glenn. Apologies, Ant - luckily someone else was reading properly.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[