Time Calculation

[kiko2max]

Hello Masters, can someone help me to build the formula of time calculation. Let say I have 1 employee, he is working from 8am to 4pm a day (8 hours/day). Then I gave him a task yesterday start 8am and finish it today on 10am. The computation it must be 10 hours only. How can i make a formula for this?

thank you masters

 

[maabadi]

Try This:

Mail Generator2.xlsm
	A	B	C	D	E	F
1		Start	End		Hours
2		11/13/2020 8:00	11/14/2020 10:00		10:00:00
3		11/14/2020 8:00	11/14/2020 15:00		7:00:00
4
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=DAYS(C2,B2)*8/24 +MOD(C2,1)-MOD(B2,1)

 

[kiko2max]

Try This:

Mail Generator2.xlsm
	A	B	C	D	E	F
1		Start	End		Hours
2		11/13/2020 8:00	11/14/2020 10:00		10:00:00
3		11/14/2020 8:00	11/14/2020 15:00		7:00:00
4
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=DAYS(C2,B2)*8/24 +MOD(C2,1)-MOD(B2,1)

Master THANKS A LOT!!! Now it is working!!!

 

[maabadi]

You're Welcome & Thanks for feedback.
And change Result column Number format to Custom and type [h]:mm:ss

 

[kiko2max]

Th

You're Welcome & Thanks for feedback.
And change Result column Number format to Custom and type [h]:mm:ss

Thank you master, my next task is to get the average time.

 

[kiko2max]

Hello master, what if i get ####### and convert it to zero? What is the formula?

 

[maabadi]

Are you have negetive results?
If Yes Then:

=IF(DAYS(C2,B2)*8/24 +MOD(C2,1)-MOD(B2,1)<0,0,DAYS(C2,B2)*8/24 +MOD(C2,1)-MOD(B2,1))

 

[kiko2max]

Yes Im getting negative time because the database is trash. Anyway thanks again. You really help me a lot. God bless