• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date conversion

sms2luv

Member
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.
 

Attachments

  • test.xlsx
    9.1 KB · Views: 6
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.
 
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
 

Attachments

  • Test.xlsx
    10.6 KB · Views: 4
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    97.3 KB · Views: 4
Back
Top