PDA

View Full Version : Solved: Lookup Dates & Times



CCkfm2000
04-28-2008, 08:06 AM
Hi,

At the time of asking for help I?m have to manually enter some dates & time in cell B2 & B4, than clicking the ?Fetch Pallet Data? button, which then get some data from a SQL database. This works.

What I?m looking for is to set up some kind of a lookup, Picking up the week no. in cell B15. This then populates automatically (cells H1:I13) the lookup range with the right dates.

I can do this manually as per my example I?ve attached.

I can get dates to increase, but the times are fixed, which I can?t add to the dates.

:help

Bob Phillips
04-28-2008, 08:22 AM
can you explain how those dates and times are calculated.

CCkfm2000
04-28-2008, 08:33 AM
Ok here we go.

Week 2 starts on Sunday 06/01/2008 19:00:00

Week 2 end on Saturday 12/01/2008 19:00:00

Each Week starting on Sunday to Saturday works like this.

Sunday night 06/01/2008 19:00 to Monday day 07/01/2008 07:00

Monday day 07/01/2008 07:00 to Monday evening 07/01/2008 19:00

Monday night 07/01/2008 19:00 to Tuesday day 08/01/2008 107:00

Etc.

What I?m looking for is to change the dates for each week. So for week 3

Would look like this.

Week 3 starts on Sunday 13/01/2008 19:00:00

Week 3 end on Saturday 19/01/2008 19:00:00

Sunday night 13/01/2008 19:00 to Monday day 14/01/2008 07:00

Monday day 14/01/2008 07:00 to Monday evening 14/01/2008 19:00

Monday night 14/01/2008 19:00 to Tuesday day 15/01/2008 107:00

Etc.

Hope this helps

Bob Phillips
04-28-2008, 09:03 AM
H1:
=DATE(YEAR(TODAY()),1,1)+(1-WEEKDAY(DATE(YEAR(TODAY()),1,1)))+7*($B$15-1)*(WEEKDAY(DATE(YEAR(TODAY()),1,1))<>1)+TIME(19,0,0)

I1:
=H1+6

H2:
=H1

I2:
=H2+TIME(12,0,0)

H3:
=H2+TIME(12,0,0)

I3:
=I2+TIME(12,0,0)

Copy H3:I3 down

CCkfm2000
04-28-2008, 09:35 AM
thanks xld


H1:
=DATE(YEAR(TODAY()),1,1)+(1-WEEKDAY(DATE(YEAR(TODAY()),1,1)))+7*($B$15-1)*(WEEKDAY(DATE(YEAR(TODAY()),1,1))<>1)+TIME(19,0,0)
I1:
=H1+6
H2:
=H1
I2:
=H2+TIME(12,0,0)
H3:
=H2+TIME(12,0,0)
I3:
=I2+TIME(12,0,0)

Copy H3:I3 down


i've added the above formulas and looks good but when I click on the fetch pallet data button I get a run-time error '-2147647259 (80004005)' any ideas on this?:dunno




:bug:

Bob Phillips
04-28-2008, 10:35 AM
I don't have SQL or an SQL database to try here, but that code seems to use your dates in B2 and B4 which we haven't touched. Which line errors? What does the SQL statement look like at that point?

CCkfm2000
04-28-2008, 10:49 AM
I don't have SQL or an SQL database to try here, but that code seems to use your dates in B2 and B4 which we haven't touched. Which line errors? What does the SQL statement look like at that point?


Sorry to mess you about - but it's was a mistake on my part. :banghead:

so far it looks very good.

will look at some more data tomorrow.

thanks for your help

CCkfm2000
04-29-2008, 06:34 AM
Thats it sorted thanks XLD. :friends: