3 Replies Latest reply on Jul 11, 2017 6:33 AM by Michael Gillespie

# Status analysis start and end date overlap month

Hi Community,

I have a question surrounding status changes. I have data for a room status eg:

StatusStart Date & TimeEnd Date & Time
A20/06/17 00:0010/07/17 14:01
B10/07/17 14:0111/07/17 18:00

I want to know the hours spend in Status A and B for June and July. I create a calculated field to calculate the elapsed time but how do I split the data over the different months?

If I use the start date all the hours are booked in the start month and if I use the end date all the hours are booked in the end month.

Can somebody help me?

Thanks,

Boy

• ###### 1. Re: Status analysis start and end date overlap month

We'll need a little more context to give you some help!

Do the start and end dates represent a room booking period?  Something like checkin date & time and checkout date & time?

If so, you'll need to have a baseline list of dates that you can compare your start and end dates to, so that you can determine if a given date falls within the start and end date boundaries.

If that's NOT what the data represents, can you give more information?  A larger data set would also be very helpful.

• ###### 2. Re: Status analysis start and end date overlap month

Yes, the analogy with a check-in and check-out date & time is correct.

A room can have 1 of several different statuses the start date and time of one status always the end date and time of the previous. I have given a small subset below. It is exception handeling most statuses are short and don't crossover into the next month however status A does and I would like to correct so that the hours are booked within the right month.

 End Date & Time Start Date & Time Status Code Room ID 17/01/2017 10:51 29/12/2016 03:08 A N18 29/12/2016 03:08 29/12/2016 02:46 H N18 29/12/2016 02:46 29/12/2016 02:23 D N18 29/12/2016 02:23 29/12/2016 01:22 H N18 29/12/2016 01:22 29/12/2016 00:48 C N18 29/12/2016 00:48 29/12/2016 00:24 D N18 29/12/2016 00:24 28/12/2016 22:01 C N18
• ###### 3. Re: Status analysis start and end date overlap month

Great.  Look at this thread: it should have what you need!