Determining Months, Weeks, Days Between Two Dates

[justcallmejohn]

Good day,
I have searched the Internet far and wide for hours, looking for a solution to my problem. Background: I am on the weak end of semi-skilled working in Access 2003. I am designing a database for a recreational vehicle park which includes check in/check out information similar to a hotel. This park charges rates based upon the length of stay. A customer receives a discounted rate for staying over a week or over a month. So, if I was to stay there from 1 January 2014 to 8 February 2014, I would pay a monthly discounted rate for one month, a weekly discounted rate for one extra week beyond the month, and then the normal rate for the remaining day. I am placing the formula directly into a query. I have found a formula that solves this problem in Excel but cannot find nor construct a corresponding formula in Access, realizing the differences in syntax between the two. In my Excel example I have a "check in" date cell in cell A1, and a "check out" date cell in B1, then three cells that calculate based upon the two input fields. They are:

D1 (to calculate months): =DATEDIF(A1,B1-(MOD(A1,1)>MOD(B1,1)),"ym")
D2 (to calculate weeks):
=INT(DATEDIF(A1,B1-(MOD(A1,1)>MOD(B1,1)),"md")/7)
D3 (to calculate days):
=MOD(DATEDIF(A1,B1-(MOD(A1,1)>MOD(B1,1)),"md"),7)

Now, in Access, I have reconstructed this scenario in a query with what I assume is the correct format for calculated fields:

Months: DateDiff('m',[CheckIn],[CheckOut])

Weeks: Int(DateDiff("ww",[CheckIn],[CheckOut]-[CheckIn] Mod (1)>[CheckOut] Mod (1))/7)

Days: Days: ((DateDiff('d',[CheckIn],[CheckOut])\7)+((DateDiff('d',[CheckIn],[CheckOut]) Mod 7)))

DaysLeftOver: [Days]-([Weeks]*7)

(The DaysLeftOver field indicates days remainder after weeks are subtracted. This field is used on my form while the Days field is hidden.)

I have tried scads of different solutions but nothing seems to work out properly. I hope that I have explained this sufficiently. Any help will not only assist me but possibly will assist others in the future. I will deeply appreciate your assistance! :banghead:

Cordially,
John
Tucson, Arizona

 

[namliam]

So, if I was to stay there from 1 January 2014 to 8 February 2014, I would pay a monthly discounted rate for one month, a weekly discounted rate for one extra week beyond the month, and then the normal rate for the remaining day.

That is one wierd pricing scheme, usually you would get a discount based on the longest time stayed, thus pay the monthly rate for this entire stay.... Sounds like peny wize pound foolish to me :/

Without digging into the formula's to much, I am guessing it is a bracket problem

 

[plog]

Can you provide sample data? Provide checkin and checkout dates for all the cases, and also provide the ultimate results you want from each pair of dates you provide.

 

[Old Man Devin]

I don't understand why you can't just the use datedif or datediff functions in their regular forms, so have lines like:
Days = DateDiff("d",[CheckIn],[CheckOut])
in Access and
Days = DATEDIF(A1,B1,"D")
in Excel. Both give the the number of days stayed in total. Works for months and weeks too, just replace d with m or w respectively. All the uses of mods and the inequalities don't make sense to me at the moment.

 

[jdraw]

It isn't really clear if you're looking for an Access or Excel solution. But this an Access forum.

 

[ebs17]

You can calculate a lot. But: There are months with 28, 29, 30, 31 days. What is a statement two months and two days worth in mathematically useful evaluation?

 

[theDBguy]

Folks, this is 9-year old thread.

 

[plog]

You picked a bad example--it's too easy. You need to think of all the places your calcualtion will get tricky:

2/16/2020 - 3/15/2020

Somewhere around there it switches from 0 months and X days to 1 month and 0 days. Where exactly is that and what general rule comes from that?

That's just one example, there's other difficult ones as well. What happens then? What's the general rule?

 

[Gasman]

Folks, this is 9-year old thread.

Yes, but a newbie resurrected it.

@lamywaby Search here @arnelgp has created several functions like this. I am sure one of them does what you want.

They were probably created as Age functions.

 

[plog]

And the post I replied to was 9 minutes old.

I think we should encourage what lamwyaby did. They actually searched and tried to find the answer on their own. They found this page and it was close but not exactly what they needed so they asked a question.

 

[theDBguy]

And the post I replied to was 9 minutes old.

I think we should encourage what lamwyaby did. They actually searched and tried to find the answer on their own. They found this page and it was close but not exactly what they needed so they asked a question.

Understood, in that case, the recommended approach is to start a new thread with a link to the old one.

Also, just FYI, the other thread this user posted in was a 13-year old discussion. Not sure yet if that means anything. I'm just keeping an eye out on things.

 

[Gasman]

Understood, in that case, the recommended approach is to start a new thread with a link to the old one.

Also, just FYI, the other thread this user posted in was a 13-year old discussion. Not sure yet if that means anything. I'm just keeping an eye out on things.

I posted in that thread as well, not noticing the age of the thread.

 

[Pat Hartman]

Here's a db with multiple date function samples including one that might help here

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

for 2/16/2020 - 3/15/2020
my sample shows 0 years, 0 months, 28 days

 

[theDBguy]

@theDBguy sorry it's my bad

No worries.