Time calculation

[Mile-O]

Also:

=(24-[StartTime])+([EndTime-12)

 

[Chimp8471]

i just tried

=(24-[StartTime])+([EndTime-12)

and it does what i am after except if the the 2 times are the same

 

[Mile-O]

A 24 hour period where the two times are the same?


If that's the assumption, then:

=IIf([StartTime]=[EndTime], 24, (24-[StartTime])+([EndTime]-12))

 

[Chimp8471]

nope i still get zero

 

[Mile-O]

I'd suggest extending your DayCode to include the Year if you are not willing to just record the Date & Time something starts and finsihes. ie. rather than 3111 you store 311103

The other problem is that the DayCode seems to represent the date started. You do not store anything about the date finished. How do you know which day the EndTime falls on?

 

[Chimp8471]

i totally understand what you are saying:

the

daycode - 3111

represents Year 03 daycode 111 hence the 3111

and it is an assumption that if the the end date will always end 24 hours after the start date

 

[Mile-O]

So 3111?

2003, is that 12th January, or 1st November?

That's why I think you should at least use six digit DayCode...

From a six digit day code you can build a date for the StartTime and EndTime:

i.e.

DayCode: 031101 (1st Nov, 2003)
StartTime 22:00
EndTime: 06:00

You can build the date from six digits:

=DateSerial("20" & Left([DayCode], 2), Mid([DayCode], 3, 2), Right([DayCode], 2))

You can build the StartDate:

=CDate(DateSerial("20" & Left([DayCode], 2), Mid([DayCode], 3, 2), Right([DayCode], 2)) + [StartTime])

Therefore, you can alse build the end time:

=CDate(DateAdd("d", 1, DateSerial("20" & Left([DayCode], 2), Mid([DayCode], 3, 2), Right([DayCode], 2))) + [EndTime])

Thus, you can finally get the duration with this big expression.

Duration: DateDiff("h", CDate(DateSerial("20" & Left([DayCode], 2), Mid([DayCode], 3, 2), Right([DayCode], 2)) + [StartTime]), CDate(DateAdd("d", 1, DateSerial("20" & Left([DayCode], 2), Mid([DayCode], 3, 2), Right([DayCode], 2))) + [EndTime]))

 

[Chimp8471]

could this then be extended to not just show the total hours but hours and minutes ???

 

[Mile-O]

could this then be extended to not just show the total hours but hours and minutes ???

Duration: TimeSerial(DateDiff("n",CDate(DateSerial("20" & Left([DayCode],2),Mid([DayCode],3,2),Right([DayCode],2))+[StartTime]),CDate(DateAdd("d",1,DateSerial("20" & Left([DayCode],2),Mid([DayCode],3,2),Right([DayCode],2)))+[EndTime]))/60,DateDiff("n",CDate(DateSerial("20" & Left([DayCode],2),Mid([DayCode],3,2),Right([DayCode],2))+[StartTime]),CDate(DateAdd("d",1,DateSerial("20" & Left([DayCode],2),Mid([DayCode],3,2),Right([DayCode],2)))+[EndTime])) Mod 60,0)

Somebody may be able to shorten it...

 

[Chimp8471]

cheers, but i still aint having any joy with this..

i have posted my DB so you can have a look.....

i have also added my Table called dated so you can see how it is currently set up.

 

[Mile-O]

Can't get into the database...do have security, groups, or anything on it?

 

[Chimp8471]

ok will sort out on my laptop then repost it


cheers

 

[Chimp8471]

i have now modified the file so hopefully u can access it,

yeah sorry i keep forgetting about the workgroups

 

[Mile-O]

Nope. Getting this...

 

[Chimp8471]

is that any better

 

[Mile-O]

There's a lot of unnecessary date in that database.

You don't need a field for Day (a reserved word in Access, anyway) as you can calculate this with: Format([UKDate],"dddd").

WeekNumber can be calculated by: CLng(Year([UkDate])*100 + Format([UkDate], "ww"))

PeriodNumber can be calculated by: CLng(Year([UkDate])*100 + Month([UkDate]))

ShiftID should be the primary key in ShiftSetup, not Shift.


It's not even relational. Two of the tables don't even have a primary key.

 

[Mile-O]

Even the DayCode (now that I understand it) is calculable from the UKDate field with a DateDiff() function.

 

[Chimp8471]

i can't thank you enough for your support here, but hopefully you will see what i am up against here, you should see the state of the rest of it........

the database was originally designed by someone who had never used Access before and has now left the company, and i have been tasked with sorting it.

that is why i really need all the help i can get