Calculate between start and end times

Lloyd · External Since: Aug 01, 2006 Posts: 15

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

Jacob Skaria · External Since: Mar 04, 2009 Posts: 442

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

Per Jessen · External Since: Jan 27, 2009 Posts: 32

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" <Lloyd DeleteThis @discussions.microsoft.com> skrev i meddelelsen
news:D5946FB0-9314-4661-BFD3-FDD644BB324F@microsoft.com...
> 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