Calculating Open Hours

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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!
example 11.png
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Understood, the formula for close date is highlighted in tan in the image above.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top