To convert PST time to CET time

[DRG1978]

Hi All,

I have in cell G4 this time (PST): 7/5/2011 0:38 and i want to have in cell R4 the time of G4 but in CET also taking in consideration the summer and winter time difference.

Is there a formula to achieve this?

I would be very grateful is someone can assist me with this!

Thanks!!

 

[Tim_Excel_]

I feel this really should not be too hard to figure out, so let me help you a hand to get you on your way

There are two things we know:

1) There is a fixed difference in time between the two time zones
2) This fixed difference changes in certain periods because of daylight savings time

A formula without taking daylight savings into account would simply add the x amount of ours necessary. However, since you DO want to take daylight savings into account, you need to determine the amount of hours the zones differ during specific periods in the year. E.g., from March to October it's a 7 hour difference, and 8 hours during the rest of the year (I don't know the actual dates/numbers).
This tells you to use IF() functions to determine the current date you're working with, with the result of that query being the amounts of hours the zones differ.

Thought of helping your thought process rather than just giving the formula

 

[DRG1978]

Hi Tim,

Thanks - i found this formula with these parameters: to add 2 hours in summer and 1 hour in winter, given that daylight saving starts at 01:00 on the last Sunday in March and ends at 01:00 on the last Sunday in October if you have a timestamp, including time and date, in cell G4 this formula will add 1 hour in winter and 2 hours in summer, for any date.

But I need to add 8 hours in summer and 9 hour in winter. Can someone assist me getting the correct formula?

G4= 7/5/2011 0:38

=IF(G4="","",IF(PRODUCT(G4-DATE(YEAR(G4),{4,11},1-WEEKDAY(DATE(YEAR(G4),{4,11},0)))-1/24),1,2)/24+G4)