Buenos días compañeros, estoy tratando de resolver como puedo crear una fórmula para modificar las fechas y horas que tengo en mi excel automaticamente usando una validación de datos con todas las zona horarias (UTC -12, UTC -11... etc). Actualmente las fechas y horas que tengo en mi excel estan en UTC -7. 

Quisiera que las fechas y horas se cambiaran automaticamente cuando seleccione otra zona horaria.

Anexo archivo para su solución. gracias!

ZONA HORARIA.xlsx

En base a lo explicado.

Las fechas y las horas ya están escritas en los rangos, si lo que buscas es que al cambiar el valor de D1 (la zona horaria) estas mismas celdas se cambien de fecha y hora sumando o restando las horas de diferencia, me parece que eso no es posible sin caer en una referencia circular porque el resultado (destino) depende del mismo valor que tiene de inicio (origen).

Considero que es posible lograrlo si agregas otras celdas para mandar el resultado ahí:

O al menos yo no sabría cómo cambiar el valor de una celda con una fórmula considerando sustituirse así misma con un resultado que depende del valor original que contenga antes del cálculo.

Por otro lado.

Si la opción del rango adicional no es factible, aún se puede considerar un código VBA que primero tome las fechas de ese rango y luego las sustituya considerando la diferencia que hay en D1

Sub AjustarFechasHoras()
    Dim ws As Worksheet
    Dim rngFechas As Range
    Dim rngHoras As Range
    Dim rngResultadoFecha As Range
    Dim rngResultadoHora As Range
    Dim i As Long
    Dim ZonaHoraria As Integer

    
    Set ws = ThisWorkbook.Sheets("Hoja1")

    
    Set rngFechas = ws.Range("D11:D16")
    Set rngHoras = ws.Range("E11:E16")
    Set rngResultadoFecha = ws.Range("D11:D16")
    Set rngResultadoHora = ws.Range("E11:E16")

    
    ZonaHoraria = CInt(Mid(ws.Range("D1").Value, 5, Len(ws.Range("D1").Value) - 4)) - (-7)

    
    For i = 1 To rngFechas.Cells.Count
    
        rngResultadoFecha.Cells(i).Value = Int(rngFechas.Cells(i).Value + rngHoras.Cells(i).Value + (ZonaHoraria / 24))
        rngResultadoHora.Cells(i).Value = rngFechas.Cells(i).Value + rngHoras.Cells(i).Value + (ZonaHoraria / 24) - rngResultadoFecha.Cells(i).Value
    Next i
End Sub

 

En tu libro de ejemplo te dejo ambas propuestas, toma en cuenta que el código VBA actualizará cada que cambies el valor en D1 o cada que des clic en el botón.

 

ZONA HORARIA.xlsb

Muchas gracias @Israel Cassales! Me sirve más el de fórmulas ya que por lo pronto en el archivo que tengo no ando incluyendo macros!

Gracias por tu aportación