CODEWITHSUNDEEP
excel
Aswini
Aswini

Reputation: 81

add hours in excel based on 8 hrs

In my excel I have the project status in hours only .

it means

M1 4 hrs
M2 6 hrs
M3 10 hrs
M4 3 hrs

I have to create a excel so that if I give a start date all the simillar dates needs to be calculated based on 8 hrs working time.

for example if my start date 09/01/2010 (this is the date entered by me)(mm/dd/yyyy)

                  Start Date               End Date
M1                 09/01/2010               09/01/2010  (4 hrs)
M2                 09/01/2010               09/02/2010  (6 hrs so talking 2 hrs from 09/02/2010)
M3                 09/02/2010               09/03/2010  (6 hrs from 9/2/2010 and 4 hrs from 9/3/2010)
M4                 09/03/2010               09/03/2010  (3 hrs from 9/3/2010)

Please any one can give me a suggestion.

Thanks, Aswini Mahesh.

Upvotes: 0

Views: 254

Answers (2)

Malik
Malik

Reputation: 9

Example: A B C

Start Date End Date Total Hours

9/15/2010 1:32 9/16/2010 1:32 24:00

9/15/2010 1:32 9/15/2010 13:32 12:00

9/15/2010 1:32 9/15/2010 7:32 06:00

9/15/2010 1:32 9/15/2010 8:44 07:12

C Cell have formula: =b2-a2+IF(a2>b2,1)

End Date - Start Date + IF(Start Date > End Date,1)

I can't attach the picture for some reason

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

You have to know that dates and time in xl are stored in days since jan-1-1900 (or any fixed reference date, it does really matter which one). This allows easy addition/subtraction.
From that you can deduct that 1 day has a value of 1, and therefore 1 hour has a value of 1/24.
To test this, enter any date or time in cell, then clear cell the format.
Once you get this, you can solve any problem.

Upvotes: 1

Related Questions