Adjusting date for UTC (1 Viewer)

[pickslides]

I have a date field in minutes, seconds always '00' i.e. 24/04/2015 1:23:00 AM
as short text

and want to adjust this date to UTC time as follows.

before 6/4/2014 2:59 add 10 hours.
between 6/4/2014 3:00 AM and 5/10/2014 1:59 AM add 11 hours.
between 5/10/2014 2:00 AM and 5/04/2015 2:59 AM add 10 hours.
between 5/4/2015 3:00 AM and 4/10/2015 1:59 AM add 11 hours.
after 4/10/2015 2:00 AM add 11 hours.

I have been using +(10/24) or +(11/24) to adjust the date and thinking of creating a separate table with the adjusted date but this will be 1440 minutes a day x 5 yrs = 2.6 M records. Seems a bit extreme.

Anyone have a suggestion..

 

[CJ_London]

look at using the dateadd function to add hours

And to make it generic, you just need to day,month and time- not the year

newdatetime=dateadd("h",iif(format(currentdatetime,"mmddhhnn") between "04060300" and "10050159",11,10),currentdatetime)

 

[pickslides]

I have

SELECT IIf([start_time] Between "6/4/2014 3:00 AM" And "5/10/14 1:59 AM" Or "5/4/2015 3:00 AM" And "4/10/15 1:59 AM",[start_time]+(10/24),[start_time]+(11/24)) AS UTC
FROM Timesheet_entries;

which gives #Error in the field. I think the format is an issue, maybe the AM part?

I also made the dates into numbers and

UPDATE Timesheet_entries 
SET Timesheet_entries.start_time = Switch(
start_time<417350.125,[start_time]+(11/24),
start_time Between 417350.125 And 419170.083333333,[start_time]+(10/24),
start_time Between 419170.083333333 And 420990.125,[start_time]+(11/24),
start_time Between 420990.125 And 422810.083333333,[start_time]+(10/24),
start_time Between 422810.083333333 And 424630.125,[start_time]+(11/24),
start_time Between 424630.125 And 426450.083333333,[start_time]+(10/24),
start_time Between 426450.083333333 And 428270.125,[start_time]+(11/24),
start_time Between 428270.125 And 430100.083333333,[start_time]+(10/24),
start_time Between 430100.083333333 And 431910.125,[start_time]+(11/24),
start_time Between 431910.125 And 433800.083333333,[start_time]+(10/24),
start_time>=433800.083333333,[start_time]+(11/24));

Which returned dates without the 10 or 11 hour adjustment

 

[pickslides]

I have combined the 2 and solved the problem. Thx for all interested

UTC: IIf([start_time] Between 417350.125 And 419170.083333333 Or Between 420990.125 And 422810.083333333 OR Between 424630.125 And 426450.083333333 OR Between 428270.125 And 430100.083333333 OR Between 431910.125 And 433800.083333333 ,[start_time]+(10/24),[start_time]+(11/24))

MQ