Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Calculate Duration Based On Start & End Times

1. I agreed to these rules
Join Date
21st January 2008
Posts
1

## Calculate Duration Based On Start & End Times

I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :-

Duration = (End Date,End Time) - (Start Date,Start Time)

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
3rd August 2007
Location
Newcastle Upon Tyne, UK
Posts
40

## Re: To Subtract Columns With Date/time Entries

Good afternoon

This formula may work for you

=IF(D2>B2,DAY(C2)-DAY(A2)&" Days and " & HOUR(D2-B2)&" hours",(DAY(C2)-DAY(A2))-1&" Days and "&(24-HOUR(B2)+HOUR(D2))&" hours")

I've attached an example document below

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: To Subtract Columns With Date/time Entries

How do you want to show the duration?

To show it in hours

=D2+C2-B2-A2

format as [h]:mm

you could use the same formula and format the cell to show days, hours and minutes by formatting as

d:hh:mm

but this won't give you correct results if you have durations above 31 days. In that situation try:

=INT(D2+C2-B2-A2)&" days "&TEXT(D2+C2-B2-A2,"h:mm")

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
29th October 2010
Posts
1

## Re: Calculate Duration Based On Start & End Times

i used this formula but my problem now, i have several sheets using this formula, i want to calculate the total number of duration of all sheets. please help! :-)

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno