Excel VBA Course
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.)

View Course

Two time values open or close

0

Hello,  

Kind of stuck on this one.

Forex Markets open all around the world from Sunday night at 1700 and close Friday 1700 (only shut 24 hours).

For example:

London opens from 0400 to 1300 hours

New York opens 0800 to 1700 hours

if my local time fall in between these times it will be "OPEN  if not "CLOSE

so it looks to see if markets are open Sunday 1700 to Friday 1700

if open then lookes at time each city open ans close time

I attached my butchered spreadsheet file

Answer
Discuss

Answers

0

After several hours of tinkering with your times I am now wondering if I understood your request correctly lol: The attached workbook requires the open and close times in columns D and F to be given in local times, not your time. The rest of what I did just reconciles those local times with your computer time in A1:B1.

Toward this end there is a column C now which shows the time differences as compared to UCT. You should enter that same difference for your own location in C3. Note that C3 will always display "UCT" but you can see the number you entered in the Formula Bar. You can hide column C thereafter.

You can also hide columns E and G. They show the converted opening and closing times. Observe that these are date/time values because with time alone you won't be able to calculate beyond midnight.

I have added a check on the open time to the formula in column H to display "Close" when the date in column E is a Sunday. The result will be less than accurate but you will probably be able to expand that formula to add more precision.

Discuss

Discussion

Did you get my solution to work for you? If you prefer to enter the opening hours of each exchange in your own local time, the required change would be small and I should be glad to help you with it if you need such help.
Variatus (rep: 4889) Dec 3, '18 at 4:06 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login