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]

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.

 

[Frank J]

I’ll need to do it on Tuesday as I’m off for the next 2 days

 

[maabadi]

And one other thing? what is minimum open hour for a table? for example : minimum 2 hours or 6 hours.

 

[Frank J]

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]

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

Table	Time	Shift	Transaction No.	Openers	Closers	User
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 ID	Open Hours	Open Date	Closing Date	Open Time	Close Time	Adjusted Close Time	Open Trans #	Close Trans #
BJ 103		11/01/20	#N/A			#N/A
BJ 105		11/01/20	#N/A			#N/A
BJ 405	19:56:09	11/01/20	FALSE	1:38:51	21:35:00	#############	10072224	10072296
BJ 407	0:45:02	11/01/20	01/00/00	1:40:00	2:25:02	26:25:02	10072232	10072254
BJ 702	0:00:00	11/01/20	11/02/20	1:59:58	1:59:58	25:59:58	10072361	10072362
BP 104		11/01/20	11/02/20
BP 106	11:42:00	11/01/20	11/02/20	12:07:11	23:49:11	47:49:11	10072279	10072300
BP 205	0:00:17	11/01/20	11/02/20	1:26:58	1:27:15	25:27:15	10072184	10072307
BP 206	0:00:14	11/01/20	11/02/20	1:27:59	1:28:13	25:28:13	10072188	10072311
BP 207	0:01:03	11/01/20	11/02/20	1:28:47	1:29:50	25:29:50	10072192	10072317
BP 302	0:05:22	11/01/20	11/02/20	1:34:15	1:39:37	25:39:37	10072204	10072333
BP 306		11/01/20	11/02/20

 

[maabadi]

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):

=D2-C2+F2-E2

 

[Frank J]

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]

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]

If your Maximum Criteria is 45 Hours, Then Change formula at B2 to:

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

 

[Frank J]

If your Maximum Criteria is 45 Hours, Then Change formula at B2 to:

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

I know it's been a while since I posed my original question and you tried to help but I've got a new look at it and have organized the data better. I've done this example by hand but need to have smart formulas that can read the rows that pertain to a specific Asset # and know when to start referring to the next Asset #.

The data is organized by each asset, by Modified Time and the last column is what I need to extract (the hours my tables are open). I've learned how to do Power Queries as well but it's pulling the data out for the individual assets when they come out of the system to excel in the format I'm showing you.

Asset #	Transaction Type	Trans ID	Year	Month	Day	Gaming Date Time	Modified Date Time	Open Hrs
BJ 405	Table Opener	10000​	2020	10	30	10/30/2020 21:10:26	10/30/2020 21:10:26
	Table Closer	10001​	2020	10	30	10/30/2020 01:42:18	10/31/2020 01:42:17	4:31
	Table Opener	10000​	2020	10	31	10/31/2020 01:42:19	10/31/2020 01:42:18
	Table Closer	10001​	2020	10	31	10/31/2020 03:14:38	10/31/2020 03:14:37	1:32
	Table Opener	10000​	2020	10	31	10/31/2020 21:02:06	10/31/2020 21:02:05
	Table Closer	10001​	2020	10	31	10/31/2020 01:38:51	11/01/2020 01:38:50	4:36
BJ 407	Table Opener	10000​	2020	10	30	10/30/2020 22:02:14	10/30/2020 22:02:13
	Table Closer	10001​	2020	10	30	10/30/2020 01:44:17	10/31/2020 01:44:16	3:42
	Table Opener	10000​	2020	10	31	10/31/2020 01:44:17	10/31/2020 01:44:17
	Table Closer	10001​	2020	10	31	10/31/2020 03:10:28	10/31/2020 03:10:27	1:26
	Table Opener	10000​	2020	10	31	10/31/2020 21:09:26	10/31/2020 21:09:25
	Table Closer	10001​	2020	10	31	10/31/2020 01:40:01	11/01/2020 01:40:00	4:30