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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]+8 Hours[/TD]
[TD]System Current time[/TD]
[TD]Remaining time[/TD]
[TD]Remaining time[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:05:20[/TD]
[TD]21:05:20[/TD]
[TD]=Now() (Time Format)[/TD]
[TD]=C1-BB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:00:00[/TD]
[TD]23:00:00[/TD]
[TD] =Now() (Time Format)[/TD]
[TD]=C2-B2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:00:00[/TD]
[TD]20:00:00[/TD]
[TD] =Now() (Time Format)[/TD]
[TD]=C3-B3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]

 

[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