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]

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!

=TEXT(<CELL1> - <CELL> - TIME(0,30,0),"h:mm")

 

[paulalex93]

=TEXT(<CELL1> - <CELL> - TIME(0,30,0),"h:mm")

Thank you so much!

 

[paulalex93]

sorry, something else I cannot figure out, is there a formula that would return the hours (after it subtracted the 30 minutes) as a number? The reason is that those hours I need to multiply by an number in another column. Thank you!

 

[HighAndWilder]

sorry, something else I cannot figure out, is there a formula that would return the hours (after it subtracted the 30 minutes) as a number? The reason is that those hours I need to multiply by an number in another column. Thank you!

=hour(<CELL1> - <CELL> - TIME(0,30,0))

 

[paulalex93]

=hour(<CELL1> - <CELL> - TIME(0,30,0))

this doesn't work because I have times that are for example 9 and a half hours and I would need to return 9.5

 

[HighAndWilder]

this doesn't work because I have times that are for example 9 and a half hours and I would need to return 9.5

=HOUR((<CELL1>-<CELL2> - TIME(0,30,0))) + (MINUTE(<CELL1>-<CELL2> - TIME(0,30,0))/60)

 

[paulalex93]

this does not work, it returns 00:00 in the cell... the previous one worked and returned the number but without the half hour if there was..

 

[HighAndWilder]

this does not work, it returns 00:00 in the cell... the previous one worked and returned the number but without the half hour if there was..

What have you got in <CELL1> and <CELL2>?

 

[paulalex93]

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.