Date conversion

[sms2luv]

Dear Team,

I am attaching excel file for reference.

I copy Date/Time from a web in format September 03, 2020 09:00 AM, this time is as per EST Time Zone. (column C)
Our organization follow PST time which is 3 hours behind EST.

I want the time to be reduced by 3 hours in column D.
I want the Hours remaining as per the current time in column E.

 

[Peter Bartholomew]

I think the first step is to turn the text strings read from the web into numerical datetimes. My system does not handle US dates (September 09 is interpreted as the 1st day in September 2009) but on your system a formula the conversion may work automatically when you treat the value as if it were a number.

In which case, subtracting 3/24 (0.125 if you prefer) from the EST datetime should give PST.

 

[Excel Wizard]

Please try at
D3
=(MID(C3,FIND(" ",C3)+1,2)&REPLACE(C3,FIND(" ",C3),4,))-3/24
Custom format
mmmm dd, yyyy hh:mm AM/PM

E3
=MAX(0,NOW()-D3)
Custom format
[h]:mm

 

[sms2luv]

Please try at
D3
=(MID(C3,FIND(" ",C3)+1,2)&REPLACE(C3,FIND(" ",C3),4,))-3/24
Custom format
mmmm dd, yyyy hh:mm AM/PM

E3
=MAX(0,NOW()-D3)
Custom format
[h]:mm

Appreciate your response,
D Column looks good, however Column E is not calculating the correct hours.
E3 shows Hours remaining as 0, however it has 5 more days remaining, so it should be ideally be more.

 

[Excel Wizard]

Maybe try
=IF(NOW()>D3, "Time pass ","Time Left ")&TEXT(ABS(NOW()-D3),"d \Da\y h:mm")

 

[sms2luv]

Excellent Solution