Convert GMT date and time to PST + misc question

[blasky]

Hi All -

I have two headaches I cannot seem to resolve.

Problem #1 -
I have a list of data, and it was collected in GMT. Id like to convert the following into PST time... including date and time. Is there a way to create a formula to that:

3/3/2017 4:56:00 (collected in GMT time) can be automatically converted into PST? The new calculation should be: 3/2/2017 21:56:00.

It does not matter that daylight savings time in the US occurred since data was collected - as long as all data is consistent... that is great. Ideally I just want it to go back 8hours and convert the date accordingly.


Problem #2 -
I have data in cell F but not on all lines. If data is in cell F1, F4, and F6 - I would like G1, G4, an G6 to all say the same thing "x". But if no data in cell F, I would like G remain empty. Is there a simple way to configure this?

Thanks everyone,

Blasky

[MrShorty]

1) Assuming all dates/times are real date/time serial numbers and not text string, it should be as simple as =GMT time stamp - TIME(8,0,0) https://support.office.com/en-us/art...e-747d0b8d5457

2) Seems like a simple IF() function https://support.office.com/en-us/art...c-aa8bbff73be2 =IF(appropriate test,"x",suitable "blank" value)

[David A Coop]

One further thought - if the data is NOT in a date/time serial number, what is the date format? If GMT is being used, are the dates in the dd/mm/yyyy format? Not possible to tell from the example you posted.

Regards,

David

[blasky]

Thanks for the follow up... the dates are being captured in the following format:

3/3/2017 5:55:00

The above is an example taken directly from my data source. It is in GMT. I would like to send it back 8 hours, so that the time is correct, and the date too! So basically just rewind the clock 8 hours and end up with the appropriate date and time.

Blasky.

[blasky]

For my 2nd issue at hand... Here is an example. For every line in Column A that has a value, I would like Column B to have a *. If Column A is blank, then I would like to leave Coulmn B blank. How is this achieved with formulas? (sorry, i think the formatting is ruining it - but basically Column B should have the * if there is a value in Column A.


Column A Column B
6 *
4.07 *

5.05 *
3.05 *

3.08 *


Thanks!

[David A Coop]

That's the same info as in your original post. I assume it is a text string! I want to know what today's date would look like. I gather there are no zeros which makes the formula more complicated.

Is it 19/3/2017 or 3/19/2017. UK dates, for example, have the day first.

[blasky]

Ahhh yes. Formatting would be the same. Todays date would be 3/19/2017. So in my example, going backwards 8 hours would be 3/2/2017 and 21:55. (or 9:55pm). Trying not to get too picky! :-)

[David A Coop]

For my 2nd issue at hand... Here is an example. For every line in Column A that has a value, I would like Column B to have a *. If Column A is blank, then I would like to leave Coulmn B blank. How is this achieved with formulas? (sorry, i think the formatting is ruining it - but basically Column B should have the * if there is a value in Column A.


Column A Column B
6 *
4.07 *

5.05 *
3.05 *

3.08 *


Thanks!

=IF(A1<>"","*","") place in B1 (assuming you start your data in A1.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[AliGW]

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[blasky]

Yes - the =IF(A1<>"","*","") formula worked great! Thank you!

[David A Coop]

Will you please attach a sample Excel workbook?

I was about ask the same thing as Ali. I'm not convinced I know how your data looks yet.

DAC

[AliGW]

Assuming your date and time string is in A1, and you want your altered date and time string in B1, try this in B1 (with B1 custom formatted to show date and time):

=A1-(8/24)