Formula to minus 3 Hours from Days, Hours & Minutes

[Stef9910]

Hello, Awesome people of this forum,

Please can someone help me with a formula to minus 3 hours from Days, Hours & Minutes?

I use the below formula to calculate the difference between two times and dates, but now also require it to minus 3 hours from the result,

=INT(K28-K27)& " Days " &HOUR(K28-K27) & " Hrs " &MINUTE(K28-K27)& " Mins "

Any ideas?

Thank you in advance

Stef

 

[Alex Blakenburg]

1 day is represented by 1 and therefore an hour is 1/24.
Since it can impact all 3 calculations you will need to apply it to all 3 elements.

=INT(K28-K27-3/24)& " Days " &HOUR(K28-K27-3/24) & " Hrs " &MINUTE(K28-K27-3/24)& " Mins "

If you had MS 365 or 2021 or later you could pull out the common part, so that it only needs to calculate once.

=LET(adjustedDuration,(K28-K27-3/24),
          INT(adjustedDuration)&" Days "&HOUR(adjustedDuration)&" Hrs "&MINUTE(adjustedDuration)&" Mins ")

 

[Stef9910]

Thank you very much, worked great

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.