conversion of schedule to another timezone

marvhielnx · Mar 18th 2018

I'm trying to convert a table of schedule from one Central Time timezone to another timezone, GMT +8:00. There's a part that when i convert series of schedules, some day/s is/are missing. First table is the original schedule in Central Time. Second table is my helper columns - the concatenated format, date and time. Third table is the converted schedule.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 86"]Central Time[/TD]
[TD="width: 65"] [/TD]
[TD="width: 65"] [/TD]
[TD="width: 71"] [/TD]

[/tr]

[tr]

[td]

Name

[/td]

[td]

Date

[/td]

[td]

start time

[/td]

[td]

end time

[/td]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]11-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]12-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]13-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]14-Mar[/TD]

[td]

Off

[/td]

[td]

Off

[/td]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]15-Mar[/TD]

[td]

Off

[/td]

[td]

Off

[/td]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]16-Mar[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]17-Mar[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]3:00 PM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]18-Mar[/TD]

[td]

Off

[/td]

[td]

Off

[/td]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]19-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]20-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]21-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[tr]

[td]

Walter

[/td]

[TD="align: right"]22-Mar[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]

[/tr]

[/TABLE]

Table 2 [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 158"]CT[/TD]
[TD="width: 137"] [/TD]

[/tr]

[tr]

[td]

03/11/2018 3:00 PM

[/td]

[td]

3/12/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/12/2018 3:00 PM

[/td]

[td]

3/13/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/13/2018 3:00 PM

[/td]

[td]

3/14/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/14/2018 Off

[/td]

[td]

03/14/2018 Off

[/td]

[/tr]

[tr]

[td]

03/15/2018 Off

[/td]

[td]

03/15/2018 Off

[/td]

[/tr]

[tr]

[td]

03/16/2018 6:00 AM

[/td]

[td]

03/16/2018 3:00 PM

[/td]

[/tr]

[tr]

[td]

03/17/2018 6:00 AM

[/td]

[td]

03/17/2018 3:00 PM

[/td]

[/tr]

[tr]

[td]

03/18/2018 Off

[/td]

[td]

03/18/2018 Off

[/td]

[/tr]

[tr]

[td]

03/19/2018 3:00 PM

[/td]

[td]

03/20/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/20/2018 3:00 PM

[/td]

[td]

03/21/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/21/2018 3:00 PM

[/td]

[td]

03/22/2018 12:00 AM

[/td]

[/tr]

[tr]

[td]

03/22/2018 3:00 PM

[/td]

[td]

3/23/2018 12:00 AM

[/td]

[/tr]

[/TABLE]

Table 3 [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 159"]GMT 8:00[/TD]
[TD="width: 135"] [/TD]

[/tr]

[tr]

[TD="align: right"]3/12/2018 5:00 AM[/TD]
[TD="align: right"]3/12/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: right"]3/13/2018 5:00 AM[/TD]
[TD="align: right"]3/13/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: right"]3/14/2018 5:00 AM[/TD]
[TD="align: right"]3/14/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="align: right"]3/16/2018 8:00 PM[/TD]
[TD="align: right"]3/17/2018 5:00 AM[/TD]

[/tr]

[tr]

[TD="align: right"]3/17/2018 8:00 PM[/TD]
[TD="align: right"]3/18/2018 5:00 AM[/TD]

[/tr]

[tr]

[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="align: right"]3/20/2018 5:00 AM[/TD]
[TD="align: right"]3/20/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: right"]3/21/2018 5:00 AM[/TD]
[TD="align: right"]3/21/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: right"]3/22/2018 5:00 AM[/TD]
[TD="align: right"]3/22/2018 2:00 PM[/TD]

[/tr]

[tr]

[TD="align: right"]3/23/2018 5:00 AM[/TD]
[TD="align: right"]3/23/2018 2:00 PM[/TD]

[/tr]

[/TABLE]

Need help on the following:

First, if it's a scheduled off, it should show date + off. Next, it should show sequential dates when converted. In the sample above, after Mar 17, it should show Mar 18 and 19, but they are missing.

Any help is greatly appreciated.

Thanks
Marvhielnx

rabsofty · Mar 18th 2018

attach your spreadsheet so we can see your code.

nyatiaju · Mar 18th 2018

Place this code in module and call it from any cell like formula.

Quote

=GMT(A1)

===================================================

Code

Function GMT(CentralTime As Variant) As Variant
Dim off As Variant


If InStr(UCase(CentralTime), "OFF") Then
    off = Split(CentralTime, " ")
    GMT = Month(off(0)) & "/" & Day(off(0)) & "/" & Year(off(0)) & " Off"
    
Else
    If Hour(CentralTime) >= 10 Then
        GMT_Date = Month(CentralTime) & "/" & Day(CentralTime) + 1 & "/" & Year(CentralTime)
        GMT_Hour = Format((Hour(CentralTime) + 14) Mod 24 & ":" & Minute(CentralTime) & ":" & Second(CentralTime), "hh:nn:ss")
    Else
    
        GMT_Date = Month(CentralTime) & "/" & Day(CentralTime) & "/" & Year(CentralTime)
        GMT_Hour = Format(Hour(CentralTime) + 14 & ":" & Minute(CentralTime) & ":" & Second(CentralTime), "hh:nn:ss")
    End If
    
    GMT = GMT_Date & " " & GMT_Hour
End If
End Function

Display More

marvhielnx · Mar 18th 2018

@rabsofty - I don't have code, just recording the steps and I forgot to ask for help for the code as well. Attached is the sample file

@nyatiaju - i tried the function but it's giving me #Name as a result.. I m using excel 2010, not sure if there's an impact on the excel version.

nyatiaju · Mar 18th 2018

See attachment for reference.

marvhielnx · Mar 18th 2018

@nyatiaju - got the file and i can see now that the function is working. Just another thing, upon conversion, Mar 14 has 2 entries and Mar 19 is missing. Is it possible to have a "checker" to check if dates are sequentially complete and no duplicate dates per name, given that I have several names with schedule?

nyatiaju · Mar 24th 2018

Hi, I have updated File to check duplicate dates per name and missing entries.

conversion of schedule to another timezone

Participate now!

Share