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

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

"Lloyd" skrev i meddelelsen
...
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

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

Try the formula
=((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
---------------
Jacob Skaria


"Lloyd" wrote:

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