asked on

Count Total Hours

Hi Experts,

I would like to request Experts help create a macro to automatically update Column D with total duration of each title. The total duration of the title is obtained by identify time deterrent between each title at column A (e.g. title 1 at 00:00hrs, title2 starts at 00:30hrs, the different is 30mins). II have manually update few sample at column D for Experts perusal. Hope Experts could help me create this macro.

Data-Consolidate.xls

normajm400

Try this formula in the column:

=IF(ISBLANK(A9),"",IF(ISERROR(A9-A8),"",A9-A8))

Open in new window

It checks for a second value to calculate and if there isn't one, leaves the cell empty.

SOLUTION

normajm400

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Hi normajm400,

Any chance to convert this formula into macro? Please assist.

Cartillo

ASKER

Hi normajm400,

I noticed the formula always skipped the last 2 titles of each date, attached the result.
Data-Consolidate.xls

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Hi ssaqibh,

Thanks for the help. The last title of each date is always empty. Have highlighted in yellow. How to fix this?
Data-Consolidate.xls

ASKER CERTIFIED SOLUTION

Peter Kwan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

Cartillo, you would have to indicate how to calculate the duration for the last line.

The following formula will calculate the difference from the first title on the next date for the last title for each date.

=IF(ISBLANK(A8),"",IF(ISERROR(A9-A8),"",IF(ISBLANK(A9),A12-A8+1,A9-A8)))

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

>> Not for points...Why not?

Cartillo

ASKER

Hi,

Thanks a lot for the help.

normajm400

Thanks barryhoudini for pointing out the "Count" function. That will be useful!

barry houdini

>> Not for points...Why not?

Hello Saqib,

It looked to me like there were existing suggestions that worked OK, my version was simply a "tweak" to those....

regards, barry

Saqib Husain

Yes, Barry the other solutions work but the beauty of your formula does deserve credit. Whenever I am stuck with a formula my first reflex is "Barry would do this with ease and elegance."

Saqib