Urgent Help Required with DateDiff

[LMK]

I used the datediff function to calculate the number of days a training event has taken:

Datediff("d",[Training Date From],[Training Date To]+1

This works for calculating training in whole days but what I really need to do is calculate when a training event only lasts for half a day (from 9am until 12pm) and more importantly when training lasts for several whole working days and half a day on the last day of training (eg show result as 3 and a half days).

I have made the 'training date from' and 'training date to' fields into date/time fields. The main problem I have is getting the datediff function to ignore the hours outwith 9am - 5pm to return the correct result. I thought I could maybe somehow count the total training time in hours, discount the non-working hours and then convert the result to whole and half days. Is this in any way possible?!

Any help would be appreciated - even if its to say its impossible and I can tell my boss it can't be done!!

Thanks.

 

[namliam]

Any help would be appreciated - even if its to say its impossible and I can tell my boss it can't be done!!

Can't be done? No such thing... Can't be done for your budget... maybe...
Or Can't be done in the time required... OK...

Anyhow, simply substract the 2 dates...
([Training Date To]-[Training Date From])
This returns something like 1.166666 for 1 day and 4 hours. and 1.5 for 1 day and 12 hours...

Offcourse if you want/need to calculate WORK days/hours, then you need something more fancy.....

 

[LMK]

Thank you for replying - its much appreciated.

I will have to calculate in work days and half work days, I'm afraid (full day 9am to 5pm, half day 9am to 12 noon). Do you know how I would go about doing this?

 

[LMK]

I am still struggling to find the solution to this - does anyone know how I can calculate the work days/half days and hours? This is all I need to find out before I am completely finished my database so I'm really keen to find the answer!

Thanks for your help.

 

[raskew]

Hi -

Think it may take some table revision to make this work, especially since a half-day could possibly occur either on the last day of the training session, or at one or more days during the training.

Would want to record each days training and not attempt to lump it all together with just a start and end date, e.g.:

tblDateTest2
- tngTitle (text}
- tngDate (Date/Time, short date)
- sTime (Date/Time, short time, i.e. military/European time where 5:00 PM = 17:00)
- eTime (Date/Time, short time, i.e. military/European time where 5:00 PM = 17:00)

Note: Used the 'short time' format just because it's easier to input and work with and we wouldn't be interested in minutes and seconds.

Would want to initially invoke the DateDiff() function specifying hours rather than days. Dividing the result by 8 will reveal the number of days, with the exception that half-days appear to be 3 hours while full days are 8 hours. Need a rounding function to round up to the nearest 1/2 day. So

Public Function RoundTo(pItem, pfactor)
'Purpose: Round to the nearest pfactor
'input:   from immediate window: ? roundto(15.35, 0.25)
'output:  15.25
   RoundTo = (Int(pItem / pfactor + 0.5) * pfactor)
End Function

So, if your table is in the above format, the following query will return the total number of days:

SELECT tblDateTest2.tngTitle
  , Sum(roundto(DateDiff("h",[stime],[etime])/8,0.5)) AS Expr1
FROM tblDateTest2
GROUP BY tblDateTest2.tngTitle;

Tested this in A97. I can provide an example in A97, if needed. Please post back.

Best wishes - Bob

 

[LMK]

Hi Bob
Thanks for your reply - it is much appreciated! If you could please provide the example you mentioned that would be great. Please forgive my ignorance - I'm a definite novice when it comes to VBA! I had originally attached the datediff function to a "no. of training days" field in a form, so I'm a bit unsure of where to attach the Public Function RoundTo? I know I haven't done this part correctly as I get an error message saying "Undefined function RoundTo in expression" when I set up the query. Also, all the data would be entered into the form, so will this allow me to enter multiple days and times for the training?
Thanks again for all your help.

 

[raskew]

LMK -

Copy/paste function RoundTo() to a new module and save the module.

Revise the query to refer to your table and fields, then try running the query. Forget about the form for the moment and just try to get the query functioning.

Please post back.

Bob

 

[LMK]

Hi Bob
I'm glad to say that the query is now functioning so thanks for your help there! Where should I go from here? I've attempted to split up the training details from the training dates and create a relationship but am not sure if this is the correct way to go about it?
Thanks again for all your help.
Lynn

 

[raskew]

Lynn -

Not quite sure where you're attempting to go with this. Would it be possible for you to post a sample database (saved in A97, if possible).

Best wishes - Bob

 

[LMK]

Hi Bob

Thanks for replying. I’m sorry that I can't post the database as I will not be back in work for a while and don’t have MS Access at home. I think it would be too large a file anyway as the training section is only one small part of a huge database. I’ll attempt to explain what I am trying to do and hopefully you could point me in the right direction.

I have a table holding all training details, ie name, dates etc. All the data related to training will be input onto a form. Originally, on the form, I had fields for Training Date From and Training Date To for the start and end of the training to be input. There was a further field on the form ‘No. of days training’ controlled by the DateDiff function to calculate the number of days training took. As you know I had this set to whole days and this is where the half day function will now come in. I think you made a good point that I should split up the dates and enter each day of the training separately. So if a training event took say 2 and a half days I would separately input the first day start time and end time, second day start and end time etc until the end of training and total them at the end to get the result 2.5.

I thought that this could perhaps be achieved by a sub-form of the main training form? I made a new table TrainingDate containing TrainingDateID (with TrainingID from the Training table as a compound key) Trainingstarttime and Trainingendtime and a further table containing TrainingDateID. I then set one to many relationships between these tables but I have obviously gone wrong somewhere as I cannot duplicate the TrainingID and Training Date ID to input the different start times and end times for the same training event. I get the error message about duplicate entries.

I am sorry this is so long-winded! I hope I have explained what I am trying to do and would be really grateful if you could offer me any more advice.

Thanks again
Best wishes, Lynn