0

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?

enter image description here

3
  • Apply [H]:mm time format on formula cell, it will consider time if exceeds 24 Hrs. Jun 29, 2021 at 4:40
  • @RajeshS sorry, I forgot to add that I did that and it came back as error
    – Teodros IV
    Jun 29, 2021 at 5:11
  • since you have not shared the Screen Shot or WB then right now difficult to say about the reason,, but what I've tried Shift Start Time: 28-06-2021 06:00:00 PM & the Shift End Time 29-06-2021 05:15:00 AM ,,, time I've use to get Remaining hrs 28-06-2021 08:00:00 PM ,,, it's NOW() and getting 9:15 ,,, Start & End Time cell Format are dd/mm/yy hh:mm:ss AM/PM and formula cell has [h]:mm ! Jun 29, 2021 at 6:49

2 Answers 2

2

You may try this also:

enter image description here

  • 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.
1
  • Glad to help you @Teodros IV ,,, keep asking ☺ Jun 30, 2021 at 2:09
2

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

enter image description here

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.

enter image description here

Please remember to press F9 to refresh time.

2
  • Nice one @Emily ,,, you get +10 ☺ Jun 29, 2021 at 13:24
  • @RajeshS Thank you very much ~~~. the same to you.
    – Emily
    Jun 30, 2021 at 0:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .