Find next non blank cell in list and return value from adjacent column

[kristy.brown]

I've been racking my brain and unable to solve what I'm sure is very simple!

I am working on a timesheet and I have a list of dates (A) [always present] and start times (B) and end time (C) [which may contain a time or be null/blank] ... In cols D and E I want to display the next scheduled start date and time where an end date is present, and null if end date not specified.

Image.png

I have all sorts of solutions going round in my head (Index Match Offset Row) but am unable to get the logic straight. i.e. Come up with a solution!

Be grateful for some input - Thanks

[blueblink01]

Hi kristy.brown ,

please attach sample worksheet to address your concern properly.

Regards,

[kristy.brown]

Hi blueblink01,

[kristy.brown]

Book.xlsxHaving established that what I was trying to do with a formula is not possible per-se I have opted for an alternative approach as ultimately what I was testing for was that a person has at least 11 hrs rest between shifts.

I am now testing against next-shift-date + time minus shift-end-date+time which works 'ish OK but I have a couple of anomilies (it doesn't work) where the next shift start is "" (so interpreted as 00:00), or the days shift finishes past mid-night which when combined with shift start date results in incorrect result.

In the attached (revised)

For example, cell o10 contains:

IF(K10<>"",(B11+D11)-(B10+K10),(B11+D11)-(B10+H10))

which I could force it to default to 24:00 (or text "over-11-hrs")

IF(D11="","24:00",IF(K10<>"",(B11+D11)-(B10+K10),(B11+D11)-(B10+H10)))

but this doesn't resolve the finishing past mid-night cell o17 scenario.

Can anyone recommend an efficient approach? Thanks

[oeldere]

J5

Please Login or Register  to view this content.

See the attached file.

[kristy.brown]

Thanks Oeldere for solving my original query ... Brilliant. Thank you

[oeldere]

Thanks for the reply.

Glad I could help.