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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please upload example for CSV file and Also Final result you want.

I think you can do it with Power Query, But I should see your files first.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
I’ll need to do it on Tuesday as I’m off for the next 2 days
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
And one other thing? what is minimum open hour for a table? for example : minimum 2 hours or 6 hours.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Theoretically there is no minimum but in a normal condition it’d be open 6-8 hours minimum. Circumstances could dictate closing at any time due to customer demand for something else or staffing issues such as sickness.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is what the raw data is like after export into CSV file format

TableTimeShiftTransaction No.OpenersClosersUser
BJ 405
1:38:51​
Day
10072224​
$32,500.00​
$0.00​
CR34039
BJ 405
21:35:00​
Day
10072296​
$0.00​
$32,700.00​
RB33366
BJ 407
1:40:00​
Day
10072232​
$75,100.00​
$0.00​
JK34648
BJ 407
2:25:02​
Day
10072254​
$0.00​
$75,100.00​
JK34648
BJ 702
1:59:58​
Day
10072361​
$0.00​
$0.00​
ATUIPELEHAKE
BJ 702
1:59:58​
Day
10072362​
$0.00​
$0.00​
ATUIPELEHAKE
BP 106
12:07:11​
Day
10072279​
$61,200.00​
$0.00​
MCVETKOVSKA
BP 106
23:49:11​
Day
10072300​
$0.00​
$60,600.00​
JPIERSON

This is what I hope to have it look like but correct data shown, I need the Table ID and Open Hours with the date so I can then move that data to a larger workbook to make my final calculations. This type of copy and paste doesn't pull the formulas but only what's in the cell. I do not have the ability to upload a true excel file as I can't use an outside downloaded program to do it. I was using the 2 Trans # as unique identifiers since the original data isn't all on 1 line but 2 for each table

Table IDOpen HoursOpen DateClosing DateOpen TimeClose TimeAdjusted Close TimeOpen Trans #Close Trans #
BJ 10311/01/20#N/A#N/A
BJ 10511/01/20#N/A#N/A
BJ 40519:56:0911/01/20FALSE1:38:5121:35:00#############1007222410072296
BJ 4070:45:0211/01/2001/00/001:40:002:25:0226:25:021007223210072254
BJ 7020:00:0011/01/2011/02/201:59:581:59:5825:59:581007236110072362
BP 10411/01/2011/02/20
BP 10611:42:0011/01/2011/02/2012:07:1123:49:1147:49:111007227910072300
BP 2050:00:1711/01/2011/02/201:26:581:27:1525:27:151007218410072307
BP 2060:00:1411/01/2011/02/201:27:591:28:1325:28:131007218810072311
BP 2070:01:0311/01/2011/02/201:28:471:29:5025:29:501007219210072317
BP 3020:05:2211/01/2011/02/201:34:151:39:3725:39:371007220410072333
BP 30611/01/2011/02/20
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I try to guide you with images. if you have problems and don't understand, Please Ask.
Write formula Based on first cell of Data and Drag it downs.
Change source cell at formulas to your source cells.
Change sheet names at formulas to your sheet names.

Sorry I forgot to write Formula for B2 at Second Images (Sheet5):
Excel Formula:
=D2-C2+F2-E2
 

Attachments

  • 1234.jpg
    1234.jpg
    214.9 KB · Views: 5
  • 1235.jpg
    1235.jpg
    225 KB · Views: 4

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My problem is the 45 hours is wrong as it can't be more then 24 hours and maybe some change because of the changeover times varying, but it can't be much longer than that. The 45 hours should be 21 hours and that's my issue with time, recognizing when to add 24 hours to get a proper time and when not to.
 

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
62
Office Version
  1. 365
Platform
  1. Windows
The closing date is part of the issue, for some it's the same date as the opener and for others it's the next day. The table with the 45 hours is opened and close on the same date, how do I get it to recognize when to make it the next date or not?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If your Maximum Criteria is 45 Hours, Then Change formula at B2 to:

Excel Formula:
=IF((F2-E2+D2-C2)>1.875,(F2-E2+D2-C2)-1,(F2-E2+D2-C2))
 

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