Hello experts... I have a large file where individuals fill in a location, date and time that they want a file sent to them. There are about 20 global cities normally requesting files. I need to convert these requested deadlines to Manila time on a master s/s. In the next round, we can ask users to identify the date, time and timezone if this is a better method. What formula can I use so that my excel will automatically convert the local times entered to Manila time? Are there any specific formats we need to use. Many thanks in advance.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Formula to convert entered times to Manila time
0
Answers
0
Selected Answer
The best way to do this is to have them select their currnet time zone and then perform a lookup that points to a table listing timezones and their offsets from Manila time and adds that offset to their time.
As far as I know there is no formula to simply switch a time to another time zone without already knowing the hours and minutes that are needed for the offset.
You can add time using a formula like this:
=A1+TIME(1,0,0)This adds 1 hour to the time in cell A1. The first argument in the TIME() function is for the hour amount; second is for the minutes; third is for the seconds.
Discussion
Thank you Don. this was very useful. In the end however, because of the setup of the file, we had to go with a macro but this helped us at least get there and understand what the formula needed to be. Many thanks!
Ilana (rep: 2) Nov 22, '16 at 2:52 pm