Question

Tine is Gabon is 8 hours ahead of Vancouver. Given any local date a user enters in one cell, and local time in another cell, have your spreadsheet determine the date/time in Gabon in the format below. For example, if the user enters 11/1/20 in once cell, 6 PM in another cell, the formula result should display November 2, 2020 2:00 AM. The formula should not display data is no date and/or time is entered.

Answer 1

First of All let us assume that the user is entering 11/1/20 in excel cell A1 and 6 PM in Cell B1.

1)Format the cell B1 to display the time 6 PM in this format hh::mm:ss . To do that go to cell B1,Right click >>Format Cell>Custom> in Type select hh:mm:ss.this will display the time as below now:

18:00:00

2) Now we will need to combine both the cells A1 and B1 into a new cell, let suppose A2 . Go to Cell A2 and enter the below formulae :-

=CONCATENATE(TEXT(A1,"mm/dd/yyyy")&" "&TEXT(B1,"hh:mm:ss"))

This will give the result in A2 as :

01/11/2020 18:00:00

You need to add 8 hours to a date/time in Vancouver to get the resultant date/Time in Goban , then apply the below formulae:

3)Select a blank cell in excel say C2, enter the formulae =A2+8/24 into the formulae bar press the Enter Key.This will give you below result in C2:

02 November 2020

Note : In the formulae A2 contains date/Time field in Vancouver , 8 is the hours you need to add to date/time field A2 to get the Date/time in Goban.The above formulae will give you the Date/Time in Goban in C2 where A2 has the date/Time in Vancouver.

4) Now format the cell C2 to give the overall result needed

Right Click C2 >> Format Cell >> Custom >>dd-mm-yyyy hh:mm

The result will look like this :

02-11-2020 02:00

Now to display this as November 2,2020 2:00 AM , go to cell C2

Right Click>>Custom>> Type enter this mmmm dd, yyyy h:mm AM/PM and press ENTER

The result would the below now :

November 02, 2020 2:00 AM