Excel Date Calculation Function New

Thasthahir

New Member
Joined
Mar 6, 2018
Messages
9
Hi There,

I have Some times ("hh:mm:ss") format in A column. In B column Have added a formula. It means i have added 8 hours based on A column data. B Column Formula : (=A2+time(8,0,0)).

Requirement:

A column time will not cross after 8 hours. If it will cross After * hours Column should be update Time Crossed commend.

IF not C column will show remaining time compare to current Time.

If current system time
22:00:00
A B C D E
Time+8 HoursSystem Current timeRemaining timeRemaining time
13:05:2021:05:20=Now() (Time Format)=C1-BB
15:00:0023:00:00 =Now() (Time Format)=C2-B2
12:00:0020:00:00 =Now() (Time Format)=C3-B3

<tbody>
</tbody>


Note: In D column Timer will be show the time if 8 hours is not crossed. If 8 hours crossed it will be show " Time Crossed"

Regards,
Mohideen Thasthahir

<tbody>
</tbody>

 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi Mohideen,

I am not 100% sure I understood exactly what you were looking for so I came up with this…




In column A: a time value entered by the user with the format “h:mm:ss”



In column B: add 8 hours to column A à =A2+TIME(8,0,0)



In column C: current time à =TEXT(NOW(),"h:mm:ss")



In column D: column B minus column C à =IF(B2-C2>0,TEXT(B2-C2,"h:mm:ss"),TEXT(ABS(B2-C2),"-h:mm:ss"))



In column E: indicate if column B is in the past à =IF(B2-C2<0,"time crossed",TEXT(B2-C2,"h:mm:ss"))



I hope that helps,



Doug



ps: time calculations in Excel do not come easy for me so there are probably more elegant ways to do this
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,638
Messages
5,549,110
Members
410,897
Latest member
Ekrupa25
Top