Using Date Ranges in a table for a query

M

Mark44stfc

New Member
Joined
Feb 18, 2013
Messages
37
I am currently building a reporting database. The data that is in there will be global but time stamps on the reporting data itself is GMT.

I therefore have to convert, also bearing in mind that clock changes happen at different times I have created a Table which is as follows

Country|Start Date|End Date|OffsetFromGMT

USA|13/03/2016 06:00 | 06/11/2016 07:00 | -04:00
USA|06/11/2016 07:00 | 12/03/2017 06:00 | -05:00

I can then build this table out for 10 years to futureproof my date time conversion

My question, is how to use this type of data in a query to convert a column? or is there a better way of achieving this?

Any heklp is greatly appreciated....
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Use a query.
the query applies the GMT to both time so both fields read as GMT.
Use that query as the basis of all date queries.

DateAdd("h",[OffsetGmt],[StartDate])
 
Last edited:
Upvote 0
ranman256 said:
Use a query.
the query applies the GMT to both time so both fields read as GMT.
Use that query as the basis of all date queries.

DateAdd("h",[OffsetGmt],[StartDate])
Click to expand...


Thanks for this, I have looked up the DateAdd function and that definiteely seems to be the way but I cannot seem to get it to work.

My conversion table has various differing conversions dependant on the date. The full table is below.

How do i then convert whereby the query will look at where the current OrgUK date sits in this table and add or subtract as appropriate


[TABLE="width: 357"]
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" span="2"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <tbody>[TR]
[TD="class: xl70, width: 108, bgcolor: silver"]ParentOrgName[/TD]
[TD="class: xl70, width: 111, bgcolor: silver"]Start Date[/TD]
[TD="class: xl70, width: 111, bgcolor: silver"]End Date[/TD]
[TD="class: xl70, width: 147, bgcolor: silver"]OffsetFromVerintGMT[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]01/01/2016 00:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/03/2016 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/03/2016 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]30/10/2016 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]30/10/2016 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]26/03/2017 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]26/03/2017 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]29/10/2017 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]29/10/2017 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]25/03/2018 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]25/03/2018 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]28/10/2018 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]28/10/2018 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]31/03/2019 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]31/03/2019 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/10/2019 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]01/01/2016 00:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/03/2016 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/03/2016 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]30/10/2016 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]30/10/2016 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]26/03/2017 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]26/03/2017 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]29/10/2017 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]29/10/2017 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]25/03/2018 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]25/03/2018 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]28/10/2018 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]28/10/2018 02:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]31/03/2019 01:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]00:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org UK FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]31/03/2019 01:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]27/10/2019 02:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]+01:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]01/01/2016 00:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]13/03/2016 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]13/03/2016 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]06/11/2016 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]06/11/2016 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]12/03/2017 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]12/03/2017 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]05/11/2017 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]05/11/2017 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]11/03/2018 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]11/03/2018 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]04/11/2018 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]04/11/2018 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]10/03/2019 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]10/03/2019 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]03/11/2019 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]01/01/2016 00:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]13/03/2016 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]13/03/2016 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]06/11/2016 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]06/11/2016 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]12/03/2017 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]12/03/2017 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]05/11/2017 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]05/11/2017 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]11/03/2018 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]11/03/2018 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]04/11/2018 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]04/11/2018 07:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]10/03/2019 06:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-05:00[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 108, bgcolor: transparent"]Org USA FO[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]10/03/2019 06:00[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]03/11/2019 07:00[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]-04:00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Access table set start of day and end of day to my schedule
Replies
2
Views
660
Tcurtis
T
F
  • Solved
Need formula for populating Date Status
Replies
7
Views
93
Joe4
Joe4
S
  • Question Question
Round down date / time field to nearest hour only
Replies
2
Views
722
surkdidat
S
A
  • Question Question
PQ Add Dynamic "Average" Column
Replies
1
Views
214
Matt Allington
M
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
305
shift-del
shift-del

Forum statistics

Threads
1,225,619
Messages
6,186,049
Members
453,335
Latest member
sfd039

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top