I'm not sure I understand the functionality of this table, but for any given value in column A like "UTC +x" or "UTC -x", the current time for that timezone - where cell A1 contains your computer's timezone (ex., "UTC -5") - would be: =IF(MID(A7,FIND(" ",A7),4)-MID($A$1,FIND(" ",$A$1),4)>=0,NOW()+TIMEVALUE(MID(A7,FIND(" ",A7),4)-MID($A$1,FIND(" ",$A$1),4)&":00:00"),NOW()-TIMEVALUE(-MID(A7,FIND(" ",A7),4)+MID($A$1,FIND(" ",$A$1),4)&":00:00"))
I think the local UTC and DST value has to be somewhere in the formula, in order to calculate what the time is for another UTC/DST specification, when the input is local time.
Local time to UTC 0 and DST 0, UtcTime = LocalTime +UtcLocal+DstLocal
Then the time for UTC -5, DST -1 is UtcTime-5-1.
With UTC -5 in A2, and DST -1 in B2 the formula is
=TIMEVALUE(TEXT(NOW()+(UtcLocal+DstLocal+VALUE(RIGHT(A2,2))+VALUE(RIGHT(B2,2)))/24,"h:mm"))
UtcLocal and DstLocal could be in cells like the others.
Use DST 0 or DST OFF 0 when no summer time.
Note that while sheet is changing, it is interested in hours AM/PM (text) and not minutes. I think prior to making all encompassing formula, a simple example should be provided, showing current area of interest, which can be used to both develop and test formula.
This appears to be a one to many problem, or a many to one - possibly experts could answer it both ways to succeed. Or could it be something else. Who is looking at clock to resolve what clock looks like to who? etc., for understanding columns, cells, where main column in current question is multifunctional. Simplification of formula may be able to use row(), for example, to achieve result. I doubt it is a one to one problem, but then again...
0
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Realizing that I can always go back to a previous version, the goal is to populate the shaded cells in Column C based on the content of Column A and whatever is placed in Column B. I have made the sheet editable. Thanks for all the help.
The following modified formula will take into account the DST setting for your own timezone (as noted in cell B2) and the DST setting of any of the other timezones. You only need to show "DST ON" or "DST OFF" to have the correct hour amount added or subtracted accordingly. Insert in C12 and copy down: =IF(MID(A12,FIND(" ",A12),4)-MID($A$2,FIND(" ",$A$2),4)>=0,NOW()+TIMEVALUE(MID(A12,FIND(" ",A12),4)-MID($A$2,FIND(" ",$A$2),4)&":00:00"),NOW()-TIMEVALUE(-MID(A12,FIND(" ",A12),4)+MID($A$2,FIND(" ",$A$2),4)&":00:00"))+((IF(RIGHT($B$2,2)="ON",1,0)-IF(RIGHT($B12,2)="ON",1,0))/24)
I edited your GoogleDocs spreadsheet; let me know if this works.
-Glenn
0
frugalmuleAuthor Commented:
Glenn, thank you sir.
ex. Fri Time AM/PM (without seconds) is all that is that is needed in Column C.
Have you had a chance to review the updated document and formatting?
If acceptible, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?. This will help ensure that future searches are meaningful to other EE members.
Thanks,
-Glenn
0
frugalmuleAuthor Commented:
Yes with the exception of the cell for my time which uses the current system time =now(). If the person looking at it is not in my time zone, then that number will be represented incorrectly.
Ah, you didn't say that there was an audience that would be in various timezones!
The fix was easy; just copy the formulas from the section below up to cell C2. Change the "My Time Zone" value in A2 and all other times change relatively. I update your GS.
-Glenn
0
frugalmuleAuthor Commented:
Thank you sir very much. I'd like to continue the conversation but see no way to ask a related question.
0
frugalmuleAuthor Commented:
Now if I had a way to have each outsourcer tell me what time was best for them in a self-explanatory way, I would have a VERY nice solution to my dilemma.
0
Featured Post
Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
=IF(MID(A7,FIND(" ",A7),4)-MID($A$1,FIND(" ",$A$1),4)>=0,NOW()+TIMEVA
Still need to apply the DST factor...
Regards,
-Glenn
(Edit: typo in previous formula}