Hour Calculations With Blanks in Table

[dharmon]

Below you will see a rough table. The HOURS column calculates the time between Start and Stop.

The Formula I am using to do this is =(([@Stop]-[@Start])-INT([@Stop]-[@Start]))*24

When there are blanks, I would like the calculation to pick up the next available time and calculate the HOURS next to the Stop time.

I Need the following to auto calculate.

15:20 to BLANK = 0.00
BLANK to 16:13 = 15:20 to 16:13 = 0.88

18:23 to BLANK = 0.00
BLANK to 19:37 = 18:23 to 19:37 = 1.23

Please advise.

Thank you kindly.

[FlameRetired]

Try this formula in the Hours column.

It is not table syntax. I do not know how to do the $F$5:F5 parts in table syntax.

Please Login or Register  to view this content.

[dharmon]

So that works within itself, but if anyone can figure a way to keep it in table syntax, that would be most helpful.

Reason being that now a macro I've used to create an insert row button is not carrying that formula with it.

Thank you for your help! It is greatly appreciated!

[FlameRetired]

dharmon,

I have been unable to find helpful info on this particular column reference structure.

I have posted a question regarding this myself moments ago.

If you would like to watch for answers too, this is the link.

http://www.excelforum.com/excel-form...25-p-5-p5.html

[dharmon]

Thank you, FlameRetired!

I am following the second thread you have created as well.

[Phuocam]

Or try this...

=IF(G5="",0,G5-LOOKUP(10^10,$F$5:F5))*24

[FlameRetired]

@ Phuocam

OP is looking for formula in Table syntax similar to this (not working) formula.

Please Login or Register  to view this content.

Needs to find how to reference $F$5:F5 with Table syntax.

[FlameRetired]

Found part of the answer here.

http://www.excelcampus.com/tips/abso...uctured-table/

Problem with Absolute References in Tables

However, there is no direct way to create an absolute reference for a table reference in a formula. By default, all table references are absolute and have the following behavior when dragged or copied:
•Formula dragged across columns: Column references change by referring to the next column to the right.
•Formula copy/pasted across: Column references remain static; do not change when copy/pasted.

When your formula needs to contain a combination of absolute and relative references, there is no way to drag or copy the formula across and keep the references correct. Dragging the formula across will make all the references change, and copy/pasting will make all the references stay the same.

I assume the same is true for rows.

Still working on an array formula.

[FlameRetired]

dharmon

This formula will have to be array-entered.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I did this in the original upload. Apparently it was VB enabled. Found no macros but had to be saved as macro enabled. I put no VBA in it.

[FlameRetired]

An afterthought.

This version does not have to be array-entered.

Please Login or Register  to view this content.

[FlameRetired]

And Tony Valko has another formula (segment) in my thread post 3.

http://www.excelforum.com/showthread...35#post4378935