Automatisk Konvertering til GMT

August 13  by Eliza

GMT er et akronym for Greenwich Mean Time, som er en referanse tid for hele verden; det er den tiden i Greenwich, England, og er noen ganger referert til som "Zulu tid." (Zulu er den fonetiske navn for null, og null refererer til lengdegrad av Greenwich, England.)

Du kan ha et behov for å konvertere en lokal tid til GMT i regnearket. Hvis du alltid vet at tiden vil bli inngått i lokal tid, kan dette gjøres ganske enkelt med en formel. For eksempel, anta at du går inn i lokal tid i celle B7, og at du er i Stillehavet tidssone. I dette tidssonen, er du enten syv eller åtte timer bak GMT, avhengig av om sommertid er i effekt. Følgende formel vil justere tiden inngått i B7 enten syv eller åtte timer, avhengig av om datoen forbundet med tiden er innen fristen på sommertid.

= HVIS (OG (B7> = DATEVALUE ("3/8/2009 2:00"), B19 <=
DATEVALUE ("11/01/2009 02:00")), B7 + 7/24, B7 + 8/24)

Husk at når du skriver inn en tid i en celle, legger Excel automatisk en dato til det. Dermed, hvis du går inn en tid med 10:15 i en celle, og den dagen du gjøre oppføringen er 17. januar da Excel konverterer automatisk oppføringen i cellen til 01/17/2009 10:15:00. Dette gjøres selv om du bare kan vise tiden i celle-i Excel, har hver dato en tid forbundet med det, og hver gang har en dato knyttet til den.

På grunn av denne oppføringen atferd, ville Excel bruke formelen bare vist seg å gjøre det riktig justering basert på standarddatoen når du skriver inn en tid (dagens dato) eller en dato du kan eksplisitt inn.

Den eneste ulempen til dette formalistisk tilnærming er at du må huske å endre sommertidgrense datoer fra år til år. (De som er i formelen er for 2009.) Du kan endre formelen, slik at du faktisk lagret grense datoer i celler, for eksempel E1 og E2, som følger:

= HVIS (OG (B7> = $ E $ 1, B19 <= $ E $ 2), B7 + 7/24, B7 + 8/24)

Mens formelen er kortere, har det fortsatt et problem med det heller statisk fastsettelse av når sommertid begynner og slutter-du må huske å oppdatere denne informasjonen manuelt. I tillegg, hvis du flytter til en annen tidssone, må du huske å endre verdiene som dato og klokkeslett blir justert.

En veldig praktisk måte rundt disse ulempene er å lage en brukerdefinert funksjon som har tilgang til Windows-grensesnittet og bestemmer hva systeminnstillingene er i datamaskinen. Din systemet holder orden på sommertid automatisk, samt hvilken tidssone du befinner deg i. Tilgang til denne informasjonen gjennom en brukerdefinert funksjon betyr at du aldri trenger å bekymre deg for disse elementene i regnearket. Du kan bruke følgende makro å gjøre nettopp det:

Option Explicit

Offentlig Declare Function SystemTimeToFileTime Lib _
"Kernel32" (lpSystemTime Som SYSTEMTIME, _
lpFileTime Som FILETIME) Som Long

Offentlig Declare Function LocalFileTimeToFileTime Lib _
"Kernel32" (lpLocalFileTime Som FILETIME, _
lpFileTime Som FILETIME) Som Long

Offentlig Declare Function FileTimeToSystemTime Lib _
"Kernel32" (lpFileTime Som FILETIME, lpSystemTime _
Som SYSTEMTIME) så lenge

Offentlig Type FILETIME
dwLowDateTime Som Long
dwHighDateTime Som Long
End Type

Offentlig Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
Wday As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Public Function LocalTimeToUTC (dteTime Som dato) Dato
Dim dteLocalFileTime Som FILETIME
Dim dteFileTime Som FILETIME
Dim dteLocalSystemTime Som SYSTEMTIME
Dim dteSystemTime Som SYSTEMTIME

dteLocalSystemTime.wYear = Cint (Year (dteTime))
dteLocalSystemTime.wMonth = Cint (Måned (dteTime))
dteLocalSystemTime.wDay = Cint (Day (dteTime))
dteLocalSystemTime.wHour = Cint (Hour (dteTime))
dteLocalSystemTime.wMinute = Cint (Minute (dteTime))
dteLocalSystemTime.wSecond = Cint (Second (dteTime))

Ring SystemTimeToFileTime (dteLocalSystemTime, _
dteLocalFileTime)
Ring LocalFileTimeToFileTime (dteLocalFileTime, _
dteFileTime)
Ring FileTimeToSystemTime (dteFileTime, dteSystemTime)

LocalTimeToUTC = CDate (dteSystemTime.wMonth & "/" & _
dteSystemTime.wDay & "/" & _
dteSystemTime.wYear & "" & _
dteSystemTime.wHour & ":" & _
dteSystemTime.wMinute & ":" & _
dteSystemTime.wSecond)
End Function

Dette kan se imponerende, som ofte er tilfellet når du arbeider med systemkall, men det fungerer fantastisk. Det er tre systemrutiner refererte (SystemTimeToFileTime, LocalFileTimeToFileTime, og FileTimeToSystemTime). Ved å sette opp samtaler og bruke dem i orden, blir dato og klokkeslett automatisk justert til GMT. For å bruke funksjonen, i regnearket ville du skriver inn dette til å konvertere tiden i celle B7:

= Localtimetoutc (B7)

Formater celle som dato / tid, og produksjonen er akkurat det du ville.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (2185) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Automatisk Konvertering til GMT.