Calculation of Time duration in hours from 24hours input data

**maxonline** · 03-30-2016, 10:58 AM

Hello Everyone. I am facing an uphill task in to creating formula to calculate duration of time taken in hours using 24hours input. Can someone show me how to do so? Thanks for your time

**JohnTopley** · 03-30-2016, 11:14 AM

Enter time as TIME format not TEXT

03:00

16:45

then time difference

=MOD(C4-B4,1)

For total format cell as [h]:mm

**maxonline** · 03-30-2016, 12:07 PM

Originally Posted by JohnTopley

Enter time as TIME format not TEXT

03:00

16:45

then time difference

=MOD(C4-B4,1)

For total format cell as [h]:mm

Hi JohnTopley

You had shown a great way to get the duration. Would it possible to tweak the formula abit to compute the duration in hours only? Example: 1hr 30min to 1.5hrs? Thanks

**Tony Valko** · 03-30-2016, 12:11 PM

Use this formula:

=MOD(C4-B4,1)*24

Format as General or Number

**Toonies** · 03-30-2016, 12:22 PM

to keep the same format try

Please Login or Register  to view this content.

**maxonline** · 04-02-2016, 10:29 AM

Originally Posted by Toonies

to keep the same format try

Please Login or Register  to view this content.

Hi Toonies

Your formula works terrific for B4 & C4. It does not work from B5 & C5 onward as they exceed into the following days.

Is it possible to calculate till next day up to 24hrs?

Eg: Time start: 2045hrs Time End: 1930hrs Duration: 22.75hrs

If the Time end is larger than Time start, it will consider completion on that day and calculate directly?

Eg: Time Start: 2045hrs Time End: 2200hrs Duration: 1.25hrs

**DBY** · 04-02-2016, 10:53 AM

Hello
If you're not going to use John's suggestion of using time formats, and using text, then you could amend Tony's idea with the TIMEVALUE function, for example:

Formula:

Please Login or Register  to view this content.

DBY

**Toonies** · 04-02-2016, 12:22 PM

The Formula does work

If you check your Start and End Times in

C5 You have input as '330 instead of 330 Remove the ' from infront of the Time

Do the same for B6, C6, C7

You have for some reason put a ' in front of your Time

**maxonline** · 04-04-2016, 10:27 AM

Originally Posted by DBY

Hello
If you're not going to use John's suggestion of using time formats, and using text, then you could amend Tony's idea with the TIMEVALUE function, for example:

Formula:

Please Login or Register  to view this content.

DBY

Hi DBY

Your solution is an exact match of what i seek. Thanks once again!!

**DBY** · 04-04-2016, 10:51 AM

Glad we could help.

Calculation of Time duration in hours from 24hours input data

LinkBack

Thread Tools

Rate This Thread

Display

Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Re: Calculation of Time duration in hours from 24hours input data

Thread Information

Users Browsing this Thread

Similar Threads

Determine Duration between Start Time and End Time Taking into consideration shift hours

Problem with calculation in a time duration formula

[SOLVED] need help with duration (cumulative time) calculation

Calculate finish time given start time, working hours and job duration

[SOLVED] Calculate Hours Between Two Dates and Times in Excel Exceeding 24Hours

[SOLVED] Sumproduct with Time Duration calculation

Duration of time for two dates in days, hours and minutes

Bookmarks

Bookmarks

Posting Permissions