Calculate time between start and finish minus the lunch break

[paulalex93]

Hello, I am trying to figure out a way to calculate the time between start and finish for shifts, I managed to do that but I need one where from those hours it subtract automatically 30 minutes. Right now I am using =TEXT(<CELL1> - <CELL2>, "h:mm").
Can someone help me please.
For e.g the hours are all starting with 07:30 and ending in different times, like 18:00 - but for this hours it returns 10:30 and I need to subtract automatically the 30 minutes break.
Thank you!

 

[HighAndWilder]

one example is this: cell1 has: 07:30 and cell 2 has 18:00 - which returns 10 hours but as 10:00 from that formula and I would need just 10. The first formula with =hour works in this case but if I have let's say 07:30 and 17:30 it should return 9.5.

Try This.

Your Cell 1 should be the end time and Cell2 the start time.

=((<End> - <Start>)*24)-((TIME(0,30,0)*1440)/60)

Need help writing script for reporting.xlsm
	D	E	F	G	H	I
1		Example 1	Example 2	Example 3	Example 4	Example 5
2	Start	7:30	8:00	8:30	9:00	9:30
3	End	18:00	18:45	19:30	20:15	21:00
4	Break	00:30	00:30	00:30	00:30	00:30
5	Duration	10.50	10.75	11.00	11.25	11.50
6	Duration Minus Break	10.00	10.25	10.50	10.75	11.00
Sheet1

Cell Formulas
Range	Formula
F2:I2	F2	=E2+TIME(0,30,0)
F3:I3	F3	=E3+TIME(0,45,0)
E5:I5	E5	=(E$3-E$2)*24
E6:I6	E6	=((E$3-E$2)*24)-((E4*1440)/60)

 

[paulalex93]

Try This.

Your Cell 1 should be the end time and Cell2 the start time.

=((<End> - <Start>)*24)-((TIME(0,30,0)*1440)/60)

Need help writing script for reporting.xlsm
	D	E	F	G	H	I
1		Example 1	Example 2	Example 3	Example 4	Example 5
2	Start	7:30	8:00	8:30	9:00	9:30
3	End	18:00	18:45	19:30	20:15	21:00
4	Break	00:30	00:30	00:30	00:30	00:30
5	Duration	10.50	10.75	11.00	11.25	11.50
6	Duration Minus Break	10.00	10.25	10.50	10.75	11.00
Sheet1

Cell Formulas
Range	Formula
F2:I2	F2	=E2+TIME(0,30,0)
F3:I3	F3	=E3+TIME(0,45,0)
E5:I5	E5	=(E$3-E$2)*24
E6:I6	E6	=((E$3-E$2)*24)-((E4*1440)/60)

=((<End> - <Start>)*24)-((TIME(0,30,0)*1440)/60) - this does not work. Is there a way to make it without having the column or row for break time. It is the same for every one, meaning 30 minutes and we do not include it in our datas. I am sorry to keep stressing out about this but I have been wrecking my brains searching for the past days in order to have that generated automatically because I already have to manually input the start and end time and I would love for the hours to be automated. I have to do this for hundreds of people daily.

 

[HighAndWilder]

=((<End> - <Start>)*24)-((TIME(0,30,0)*1440)/60) - this does not work. Is there a way to make it without having the column or row for break time. It is the same for every one, meaning 30 minutes and we do not include it in our datas. I am sorry to keep stressing out about this but I have been wrecking my brains searching for the past days in order to have that generated automatically because I already have to manually input the start and end time and I would love for the hours to be automated. I have to do this for hundreds of people daily.

Can you please use XL2BB to submit a mini sheet of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[paulalex93]

Can you please use XL2BB to submit a mini sheet of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Can you please use XL2BB to submit a mini sheet of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

mrexcel.xlsx
	B	C	D	E	F	G	H	I
1	NAME	ROLE	CLOCK IN	CLOCK OUT	HOURS	NOTES	sum	NOTES
2			7:30	18:00	10:00	<- here I need the hours as a number	here I will make a formula which looks like this: =F2*32.5 because I need the hours multiplied by that sum
3			7:30	18:00	10:00
sheet 1

Cell Formulas
Range	Formula
F2:F3	F2	=TEXT(E2-D2 - TIME(0,30,0),"h:mm")