Calculate remaining time from end time that lies on next date

Shift begins in evening and ends at 05:00 in the morning

I am looking to calculate in real time, how much time is left till end of shift.

I have been using =NOW() in hh:mm format to calculate and end of shift left it at 05:15 in hh:mm as shown below. Obviously end of shift will be +1 day from start of shift date.

Any way to go around calculating how many hours left almost like a countdown?

Solution 1:

You may try this also:

• Formula in cell AG14:

  =TODAY()+AG10+(24/24)-AG11

N.B.

• cell AG11 has NOW(), and cell format is dd-mm-yyyy hh:mm it may be dd-mm-yyyy hh:mm AM/PM also.
• Where AG+(24/24) adds 24 Hours to Today() & returns 30/06/2021 05:15:00, and is adjustable.
• Cell AG14 has cell format hh:mm:ss.

Solution 2:

Here is my sample, I set cell with "Now()" as a date format, set the cell with "05:15" as hh:mm.

Then I use the formula =IF(B2>(TODAY()+TIME(5,0,0)),(24+TODAY()+TIME(5,15,0))-B2,(TODAY()+TIME(5,15,0))-B2) to get the remaining time.

Please remember to press F9 to refresh time.