Help Converting duration

[Trident_muh]

Total pending time duration conversion.

Column A1 is Order date, Column B1 Success date, Column C1 duration (duration format is hh:mm:ss) and Column D1 is the total pending time.

Example:

Column A1 (10/3/2023 14:52:14), Column B1 (10/4/2023 16:08:00), using the formula (=B1-A1) in Column C the result of the duration is 25:15:46. Now i want to convert the duration to total pending time but only the number of days and hours in Column D1. example: 1 day and 1 hour. what formula should i use?

 

[🤖]

To convert the duration to total pending time showing only the number of days and hours, you can use the following formula in cell D1:

=INT(C1)&" day(s) and "&HOUR(C1)&" hour(s)"

This formula uses the INT function to extract the number of days from the duration and the HOUR function to extract the number of hours. The result is then concatenated with the corresponding text.

Applying this formula to your example, assuming the result of the duration calculation is in cell C1, the formula in cell D1 would be:

=INT(C1)&" day(s) and "&HOUR(C1)&" hour(s)"

And the result would be "1 day(s) and 1 hour(s)".

Note: This formula assumes that the duration is less than 24 hours. If the duration can exceed 24 hours, you may need to adjust the formula to handle cases where the duration spans multiple days.

 

[Trident_muh]

To convert the duration to total pending time showing only the number of days and hours, you can use the following formula in cell D1:

=INT(C1)&" day(s) and "&HOUR(C1)&" hour(s)"

This formula uses the INT function to extract the number of days from the duration and the HOUR function to extract the number of hours. The result is then concatenated with the corresponding text.

Applying this formula to your example, assuming the result of the duration calculation is in cell C1, the formula in cell D1 would be:

=INT(C1)&" day(s) and "&HOUR(C1)&" hour(s)"

And the result would be "1 day(s) and 1 hour(s)".

Note: This formula assumes that the duration is less than 24 hours. If the duration can exceed 24 hours, you may need to adjust the formula to handle cases where the duration spans multiple days.

Please provide formula that exceeds 24 hours

 

[🤖]

Please provide formula that exceeds 24 hours

Apologies for the confusion. If you want to convert the duration to total pending time exceeding 24 hours, you can use the following formula in cell D1:

=INT(C1)&" day(s) and "&MOD(HOUR(C1), 24)&" hour(s)"

This formula uses the INT function to extract the number of full days from the duration. The MOD function is used to calculate the remaining hours after dividing by 24.

Applying this formula to your example, assuming the result of the duration calculation is in cell C1, the formula in cell D1 would be:

=INT(C1)&" day(s) and "&MOD(HOUR(C1), 24)&" hour(s)"

And the result would be "1 day(s) and 1 hour(s)".

This formula can handle durations that exceed 24 hours and will display the correct number of days and hours.

 

[Trident_muh]

Apologies for the confusion. If you want to convert the duration to total pending time exceeding 24 hours, you can use the following formula in cell D1:

=INT(C1)&" day(s) and "&MOD(HOUR(C1), 24)&" hour(s)"

This formula uses the INT function to extract the number of full days from the duration. The MOD function is used to calculate the remaining hours after dividing by 24.

Applying this formula to your example, assuming the result of the duration calculation is in cell C1, the formula in cell D1 would be:

=INT(C1)&" day(s) and "&MOD(HOUR(C1), 24)&" hour(s)"

And the result would be "1 day(s) and 1 hour(s)".

This formula can handle durations that exceed 24 hours and will display the correct number of days and hours.

It works. thank you

 

[🤖]

It works. thank you

You're welcome! I'm glad to hear that the formula works for you. If you have any more questions, feel free to ask. Happy to help!