Find Dynamic date

[rickyckc]

Hi all,

Is it possible by functions or VBA, to find dates like, example,

2nd Monday of October,

Last Friday (of the month) for September,

3rd Sunday of April,

etc etc ?

Thanks.

Best Regards,
Ricky

 

[Harvey]

yes it is. Unfortunately, you have to give us a little bit more information about what you want to accomplish before we can give you good examples. Take a look at the VLOOKUP formula, or the VBA function Range.Find

 

[rickyckc]

Hi Harvey,

I've made a simple world time......with rows of cities, and a column showing their local time. What i need now is based on the DST settings, the local time will adjust accordingly when the date comes

Best Regards,
Ricky

 

[Harvey]

you want to change the local time of the user computer to the time selected in excel?
or just put the time/dat from the selected cell in another cell?

 

[rickyckc]

Hi Harvey,

I just want to change the cell,

example i have,

A B C D E
Baltimore -5 4pm 1st_Sun_of_Apr Last_Sun_of_Oct

and when 1st Sunday of April arrives .....it will add 1 hr and shows,

A B C D E
Baltimore -5 5pm 1st_Sun_of_Apr Last_Sun_of_Oct

and when Last Sunday of October arrives .....it will minus back 1 hr

Best Regards,
Ricky

 

[Harvey]

I'm sorry to ask you again, but I am still not sure if I understand what you need. (had a rough time last night )

you have three cells, lets say, lets call them A, B and C.
in A is a date.
in B is a time.
in C is a condition for summertime (1st_Sun_of_Apr Last_Sun_of_Oct)

now you want to have B contain +1 hour if it matches the condition in C (first sunday of april till last sunday of october)

Theoretically, this is best done with a formula in another cell, for instance, D.

Now I have this questions left:

are the conditions all formulated in the same way?
(ie. do they start all on sunday, are they all like
1st_sun_of_apr last_sun_of_oct
1st_sun_of_mar last_fri_of_dec
3th_tue_of_jun 2nd_mon_of_may)

how many date are in the sheet?
(can you maybe post an example, using the html maker?)

 

[rickyckc]

Hi Harvey,

Sorry, I am in office now and the file is back in my home pc, so I cannot make a html.

The DST settings are different, eample...

Adelaide = Last Sunday of October - Last Sunday of March
Amman = Last Thursday of March - Last Thursday of September
Alexandria = 1st Saturday of May - Last Wednesday of September

etc, etc

It does not matter if the formula is in another cell/column.

I have total 602 cities and of course not all have DST

Best Regards,

 

[Harvey]

=RIGHT(LEFT(B1,FIND("-",B1)-2),LEN(LEFT(B1,FIND("-",B1)-2))-MIN(FIND("thisisamonth",SUBSTITUTE(LEFT(B1,FIND("-",B1)-2),{"januari"\"februari"\"march"\"april"\"may"\"june"\"july"\"august"\"september"\"october"\"november"\"december"},"thisisamonth")&"thisisamonth"))+1)

this function will return the month of the left end of the condition.
I am wondering if it's better to do this in VBA
Looking forward to seeing your sheet

 

[rickyckc]

Hi Harvey,

I did not use your last code. Thanks anyway.

What I did was used codes writen by other people here and there and managed to get what I want.

Want me send you the excel file for your viewing of what I was trying to archiev ? If you want, just let me know your email addy.

Thanks again for your help.

Best Regards,
Ricky

 

[Harvey]

Hi,

If you already finished it there is no need anymore to send it I think. But if you wish, I can have a look at it and judge your solution xD my email is s205787@student.windesheim.nl