Hours on site from gate time list

[mummbles74]

I have a list of gate times that are downloaded from a clients site access system, they want us to use these time to confirm our invoiced hours. There has been a system update and the data I receive is now in a RAW format and it creates an extremely manual task doing this for everyone on site. I have created some formulas to make it a little easier but there is still a large number of manual inputs that I need to do. Below is how I receive the data, this is the only required data from the 20 something columns. What I would like to be able to do and I have a feeling it is possible from reading some other posts is copy the earliest time from the day put it in a column of a new sheet and the latest time from the day in the next column so that I can then calculate the time.

Cardholder Transaction History Report

Date (>=)	01/02/2022 00:00:00​
Date	01/02/2022 06:22:13​
Date	01/02/2022 14:03:43​
Date	01/02/2022 14:56:44​
Date	01/02/2022 16:31:00​
Date	02/02/2022 06:21:51​
Date	02/02/2022 11:01:56​
Date	02/02/2022 13:59:47​
Date	02/02/2022 16:30:14​

 

[HAREA93]

=IF(J7<I7,HOUR(J7)-HOUR(I7)+24,HOUR(J7)-HOUR(I7))+(MINUTE(J7)-MINUTE(I7))/60
I is your start time cell, J is your finish time cell (change to your cell refs)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JamesCanale]

No sure if I understand the requirements right, but here is an Office365 and older version of excel solution.

MrExcelPlayground7.xlsx
	A	B	C	D	E	F	G
1				Office 365
2	Date	2/1/2022 6:22			min	max	Time
3	Date	2/1/2022 14:03		2/1/2022	2/1/2022 6:22:13	2/1/2022 16:31:00	10:08:47
4	Date	2/1/2022 14:56		2/2/2022	2/2/2022 6:21:51	2/2/2022 16:30:14	10:08:23
5	Date	2/1/2022 16:31
6	Date	2/2/2022 6:21		Older Excel
7	Date	2/2/2022 11:01		2/1/2022	2/1/2022 6:22:13	2/1/2022 16:31:00	10:08:47
8	Date	2/2/2022 13:59		2/2/2022	2/2/2022 6:21:51	2/2/2022 16:30:14	10:08:23
9	Date	2/2/2022 16:30
Sheet16

Cell Formulas
Range	Formula
D3:D4	D3	=UNIQUE(INT(B2:B9))
E3:E4	E3	=MIN(FILTER($B$2:$B$9,INT($B$2:$B$9)=D3))
F3:F4	F3	=MAX(FILTER($B$2:$B$9,INT($B$2:$B$9)=D3))
G3:G4,G7:G8	G3	=F3-E3
E7:E8	E7	=MIN(IF(INT($B$2:$B$9)=D7,$B$2:$B$9,""))
F7:F8	F7	=MAX(IF(INT($B$2:$B$9)=D7,$B$2:$B$9,""))
D8	D8	=D7+1
Dynamic array formulas.

 

[mummbles74]

Thank you all for the help, apologies for not sharing enough information. What I would like to be able to do is create a sheet as quickly as possible as there are a large number of staff on site. I am happy to use VBA, formulas or any other solutions that people can suggest. ideally the output would be a sheet that had all of the days worked (the date) the start time (earliest entry) and then the finish time (latest entry for that date).

I can see that @JamesCanale formulas may well give me the solution I am looking for. So will do some trialling with that.

I am using 365 version of Excel on a windows machine.

 

[Fluff]

I am using 365 version of Excel on a windows machine.

Please update you profile to show this information. It saves members having to ask every time.