Calculating Open Hours

[Frank J]

I'm trying to calculate how many hours a game is open and the correct date it closed on. We work our day from 2 am to 1:59 am but the exact times are never the same for each table as they must be counted individually.

The "Data Import" area is from a report I run and gives only the data shown, I need to reformat it and do the calculations to get the hours and minutes the tables are open.
The "26 hour clock" is an attempt to help do this but isn't a must if the formulas can be made to work.

In my current calculations you can see row 7 "BP 205" shows only 17 min but it's really 24 hours and 17 minutes. I've tried to create an adjusted time to account for the fact tables that close between midnight (00:00;00) and the time we count them are on recorded for the "Gaming Day" previous that "calendar day".

Any guidance and help is appreciated!

 

[maabadi]

Change number format at that column to Custom and write at Type section:
[h]:mm:ss
or select this from list.
Also You can add formula to do more than 24 hours if you want.

 

[maabadi]

And I think if your first open date is cell C3 this formula should work:
=IF(DAY(C3)=DAY(D3),F3-E3,DAY(D3)-DAY(C3)+F3-E3)

 

[Frank J]

And I think if your first open date is cell C3 this formula should work:
=IF(DAY(C3)=DAY(D3),F3-E3,DAY(D3)-DAY(C3)+F3-E3)

which cell are we putting that formula in? One issue is the close Date is not right and is being calculated by looking at the Close Time which is a hard fact from my system. My formula for that is an issue. Putting the formula you give in the Closing Date cell gives me a "False" return, what am I doing wrong?

 

[maabadi]

This formula is for result of total time format ( I think it was column B) that you said 24 hours and 17 sec shows 17 second.
And what is your formula for closing date.

 

[Frank J]

Understood, the formula for close date is highlighted in tan in the image above.

 

[maabadi]

Please write your columns name. I don't know what it is. if your adjusted close time is column G .For Column G at G3 write ( Change number format at that column to Custom and write at Type section: [h]:mm:ss ):
=IF(DAY(C3)=DAY(D3),F3,F3+DAY(D3)-DAY(C3))

For other columns Formula, Please write your column names (A,B,C,D,....)

 

[Frank J]

Columns are named A- Table ID,
B- Open Hours,
C- Open Date,
D- Closing Date,
E- Open Time,
F- Close Time,
G- Adjusted Close Time

When I try your formula in column D it returns 1/0/1900 and if I try it in column G I get a negative number. Both columns are formatted as you said as well, what's my solution sir?

 

[maabadi]

All Number format at Open time & Close time (Columns of E & F ) should be changed to Time format.
All Number format at Open Date & Closing Date (Columns of C & D ) should be changed to Date format. (Short Date)
All Number format at Open Hours & Adjusted Closing Time (Columns of B & G ) should be changed to [h]:mm:ss format.
Then Cell G3 equal : =IF(DAY(C3)=DAY(D3),F3,F3+DAY(D3)-DAY(C3)) and drag it down
And Cell B3 equal: =G3-E3
Why you have formula for closing date? I think you should enter by hand not formula.
If you want enter data bigger than 24 hours at column F (Closing Date), you should change its format to [h]:mm:ss . Then
1.You can remove Adjusted closing time Column.
2. Change formula at B3 equal to: =F3-E3
3. Add formula to Closing Date. Then Cell D3 Equal to: =if(F3>=1, C3+1, C3)

 

[Frank J]

My issue with dates is the report that pulls the data from the system is poorly written and doesn't give actual calendar as we are a casino and use a Gaming Day of 2am to 2am, this is tracked by doing a physical count of the table's inventory of chips but it's never at exactly the same time or duration. There are many variables and I need to be able to run the report, export it to excel (in CSV format) then bring it into a format I can use. This needs to be done daily on a continuous basis and I need as much automated as possible to facilitate the calculation of how long a table is open so I can then calculate how much revenue we generate on each table by hour.

In it's raw form there are no dates just times but I know the date as I run the report for an individual date. In my image the "Data Import" section to the right is the raw data I pull in from the CSV file and I'm trying to convert it into a useable format and get the hours a table is open. I need to do it for every day.