# Thread: Divide time period over days

1. Registered User
Join Date
Sep 2015
Posts
2

## Unanswered: Divide time period over days

SOLVED; Just after writing this post I found a way to simulate excel formula in SQL.

Hi, I started a few months ago with SQL and VBA as excel became to slow to handle the data. Most of the files I converted already but I have now to start with the more complex ones :-(.

I have following in excel (workbook with tripdata I use for invoicing the haulier):
Basis data (export from other system) : Trip ID, StartDate, StartTime, EndDate, EndTime
Then formula fields : Weekday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

Based on the basis data I first define the weekday on the startday, then I allocate the time to the correct day column or colums if EndDate > Startdate.

Has anyone an idea how to do this via SQL or VBA ?

Jan
Last edited by JanLofgren; 09-04-15 at 16:41. Reason: Solved

2. Registered User
Join Date
May 2005
Location
Posts
2,888
Welcome to the site. Posting your solution may help somebody else in the future.

3. Registered User
Join Date
Sep 2015
Posts
2
Originally Posted by pbaldy
Welcome to the site. Posting your solution may help somebody else in the future.
Paul, you're right. It could help others.

Below the SQL I translated from the formulas I had in excel

UPDATE Table
SET Table.Weekday = Weekday([StartDate],2),
Table.Monday = IIf((Weekday([StartDate],2)=1 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=1 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)-6=1 And [EndTime]<[StartTime]),[EndTime],0),
Table.Tuesday = IIf((Weekday([StartDate],2)=2 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=2 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)-1=2 And [EndTime]<[StartTime]),[EndTime],0),
Table.Wednesday = IIf((Weekday([StartDate],2)=3 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=3 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=3 And [EndTime]<[StartTime]),[EndTime],0),
Table.Thursday = IIf((Weekday([StartDate],2)=4 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=4 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=4 And [EndTime]<[StartTime]),[EndTime],0),
Table.Friday = IIf((Weekday([StartDate],2)=5 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=5 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=5 And [EndTime]<[StartTime]),[EndTime],0),
Table.Saturday = IIf((Weekday([StartDate],2)=6 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=6 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=6 And [EndTime]<[StartTime]),[EndTime],0),
Table.Sunday = IIf((Weekday([StartDate],2)=7 And [EndTime]>[StartTime]),[EndTime]-[StartTime],0)+IIf((Weekday([StartDate],2)=7 And [EndTime]<[StartTime]),1-[StartTime],0)+IIf((Weekday([StartDate],2)+1=7 And [EndTime]<[StartTime]),[EndTime],0);