Filling in a Start Time based on End Time of previous event

Emil Vaintrub

Hi All,

Would appreciate your assistance on the below -

I have a grid that contains the following columns -

1) Category

2) Audience

3) Start Time

4) Start Time (For Header)

5) End Time

6) Order

7) Date

8) Duration

The user inputs first inputs the Date, Duration, Audience and Category.

Category can be a Header or something else. If Header, that means that is the meeting title. Other categories, will be the meeting topics.

Order is set for all lone items where Header would be 1 and the rest under the same Audience would follow.

Audience is a dropdown with 3 set values.

Start Time should be submitted for Header only in Start Time (For Header) column and item #2 would receive the same Start Time.

Then item #3 would fetch End Time for item #2 as Start Time and so on.

I have helper column to calculate End Time which works perfectly.

The problem - when writing the formula for Start Time to be filled automatically, it returns #CIRCULAR REFERENCE since I refer to the End Time column - The

Would appreciate any ideas how to overcome this error.

The formulas that exists in the grid -

Start Time (Suggested Formula which works in separate column)

=IFERROR(IF(Category@row = "***Header***", [Start Time (Header)]@row, IF([Order (For Report)]@row = 2, (INDEX(COLLECT([Start Time (Header)]:[Start Time (Header)], Category:Category, @cell = "***Header***", Audience:Audience, @cell = Audience@row, Date:Date, @cell = Date@row), 1)), INDEX(COLLECT([End Time]:[End Time], [Order (For Report)]:[Order (For Report)], @cell = [Order (For Report)]@row - 1, Date:Date, @cell = Date@row), 1))), " ")

End

=IF(Category@row = "***Header***", IFERROR(IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + ([Duration (Min)]@row / 60), " "), IFERROR(IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + ([Duration (Min)]@row / 60), " "))

End Time

=IFERROR(IF(MOD(INT(End@row), 12) = 0, 12, IF(End@row < 10, "0") + MOD(INT(End@row), 12)) + ":" + IF((End@row - INT(End@row)) * 60 <= 9, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "PM", "AM"), " ")

Thanks in advance!

Emil Vaintrub

Would appreciate any idea :)

Courtney S.

It seems like you are storing different kinds of information in the same column(s). Instead of trying to have header rows, I think this would be a lot easier if you have a column for storing the header, and the header start and end times would have their own columns too. And then have columns for storing the information specific to each meeting topic. All the meeting topic rows for that header would have identical answers in their header/header start/header end columns.