Forum Discussion
Time between date look up and return value
Hello
I have some data which I need to review. I've been given an annual calendar of trip dates. I need to review these dates for periods over 30days. I.e. if first day is 5th November and the next is 1st Jan. This would be equal to and greater than 30days and therefore I'd need to highlight it somehow and then return the exact number days between the date range. I attach some sample data to show what I mean.
The order of each section isn't very friendly in that its first section annual run of trips and then second section in the rows after. The dates are sequential and it is the dates between that I'm I need to know.
What's the best way to review this?
The data that has been sent isn't structured all that well, so I wonder if a tidy up first is needed?
Thank you very much in advance!
Hikimbirch2,
good that you asked. By your change at the beginning of the line it would not have worked. In addition, there was a change in the structure of the data. The line below Trip # was no longer empty but already contained the first record. Therefore I changed the structure now completely and worked with filters. But you also got a summary of the data > 30 days. I hope you like it.
9 Replies
Hi kimbirch2,
none of your trips has lasted more than 30 days. 26 days is the maximum. Please look at my table with a suggested solution. Does this correspond to your question? Possibly I did not understand something correctly.
I have left all the auxiliary columns so that you can easily understand the approach. I hope it fits like this.
- Hi dscheikey - thanks for your quick review of the data. Yes this seems to follow what I need. True or False there has been equal to or greater than 30 days between the above and below dates. I have ALOT more data in this format. Does it work to drag this below. I don't want to fall foul of assuming it's worked OK. See V2 attached.
Hikimbirch2,
good that you asked. By your change at the beginning of the line it would not have worked. In addition, there was a change in the structure of the data. The line below Trip # was no longer empty but already contained the first record. Therefore I changed the structure now completely and worked with filters. But you also got a summary of the data > 30 days. I hope you like it.
