Calculate between start and end times

[Lloyd]

Good day
May name is Lloyd, I would like to thank you in advance.
I have a spreadsheet to calculate which has Col A= Start Date. Col B= Start
Time. Col C= Start Time. Col D= End Time.

I would like to calculate the total hours and minutes between Col C and Col
D also looking at Col A and B. then minus 30 minutes every 5Hours.

Thanking zyou

Lloyd

 

[Per Jessen]

Hi Lloyd,

You say Col C=Start time, I assume it is a typo and Col C =End Date.

Not sure if you want to subtract 30 minutes for each started 5 hours or each
full 5 hours. In the formula below I assume the first option is what you
want. If you want the second option, substitute Roudup with Rounddown:

=((C2-A2)+(D2-B2))-(ROUNDUP(((C2-A2)+(D2-B2))/TIME(5,0,0),0)*TIME(0,30,0))

Remember to format the result cell as custom: [hh]:mm to show more than 24
hours.

Regards,
Per

 

[Jacob Skaria]

A= Start Date. Col B= Start Time. Col C= Start Time. Col D= End Time.

Try the formul
=((C1+D1)-(A1+B1))-(FLOOR(((C1+D1)-(A1+B1)),TIME(5,0,0))/TIME(5,0,0))*TIME(0,30,0)


If this post helps click Yes