Time Reference data

[teststrip]

Looking to solve a couple of Date and Time reference problems.

Attached is the numbers spreadsheet. At the right I need to create formula's to calculate the amount of TT in the past 24 hours, TT in the current quarter, TT in the Current and last quarter, TT in the current calendar year.

I would assume they would all be solved useing the same method just changing the references. My issue I have been having is to get the Month, Day reference to work backwards.

Anyone good with Date and Time Reference formulas?

[Glenn Kennedy]

Is it intentional that some of the dates are in the future?

[Glenn Kennedy]

I have made an assumption that there are NO future dates I this an that the references to Nov and Dec 2014 were a mistake. on that basis, this now works.

BUT now that I look at the last couple of columns I h=get a feeling that this may include dates in the future. Can you confirm one way or the other and I'll see what can be done. No doubt others can come up with more elegant formulae, but these do work...

[teststrip]

Hi Glenn,

Yes, I do need it to reference future dates as well for forecasting purposes.

Just to recap, I need to get the TT from Stop date and time going back 24 hours, TT from Stop Date and time going back to the beginning of present quarter, then going back to present 2 quarters, and then present calendar year.

Thank you for the help. Working on this for two days and just having a hard time referencing the date time reference looking back.

[Glenn Kennedy]

OK, now I need some help from you. Open the sheet. For events that are WHOLLY in the future, (yellow shading) they won't count for the backward look. In V1 of my sheet, they would have counted. I knew that was a minor flaw, which is why I raised the question.

However...

look at the brown shaded row. This one straddles the present date. How do you want it to be counted? As wholly in the future (probably easy), as wholly in the past (also probably easy) or split between past and present - based on the current date/time (probably NOT AT ALL easy).

Over to you...