Excel Date Calculation Function New

[Thasthahir]

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 Hours	System Current time	Remaining time	Remaining time
13:05:20	21:05:20	=Now() (Time Format)	=C1-BB
15:00:00	23:00:00	=Now() (Time Format)	=C2-B2
12:00:00	20: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>

 

[duggie33]

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