Solved All months of year=30 (1 Viewer)

[FahadTiger]

Hi Expert..
Is there a function through which all months can be considered to have 30 days, so that when I subtract a first date from a second date, the months are considered to have 30 days? so i get the Result of days
I can do it with a query but can it do it with vba
thank you All

 

[Gasman]

No , why would there be?

 

[plog]

o i get the Result of days

I don't understand that exactly. But hey, has that ever stopped anyone on this forum? Use the DateDiff to find total days:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

RemainingDays: DateDiff("d", [StartDate], [EndDate]) MOD 30

Then get the remainder when you divide by 30 using MOD.

MS Access: Mod Function

This MSAccess tutorial explains how to use the Access Mod function with syntax and examples. The Microsoft Access Mod function returns the remainder after a number is divided by a divisor.

www.techonthenet.com

 

[moke123]

Hi Expert..
Is there a function through which all months can be considered to have 30 days, so that when I subtract a first date from a second date, the months are considered to have 30 days? so i get the Result of days
I can do it with a query but can it do it with vba
thank you All

just curious, why wouldn't you want the actual number of days?

 

[theDBguy]

I can do it with a query

Can you show us how? Just curious...

 

[FahadTiger]

Can you show us how? Just curious.

This is my attempt at querying

 

[FahadTiger]

just curious, why wouldn't you want the actual number of days?

The customer asked me to

 

[FahadTiger]

RemainingDays: DateDiff("d", [StartDate], [EndDate]) MOD 30

it works perfect when difference up to 3 months ..but it less than 30 days when difference between 1 to 3 months

 

[theDBguy]

This is my attempt at querying

Will this work?

SELECT id, date1, date2, DateDiff("m",date1,date2)*30 AS dT FROM Table1

 

[FahadTiger]

Will this work?

no...

 

[theDBguy]

no...
View attachment 107113

How about this one?

SELECT Table1.id, Table1.date1, Table1.date2, DateDiff("m",[date1],[date2])*30+(Day([date2])-Day([date1])) AS dT
FROM Table1;

 

[plog]

I don't understand, what do you expect from the example you listed. Better yet, give me expectations for these 5 examples:

2022-01-01, 2022-01-29
2022-01-01, 2022-01-30
2022-01-01, 2022-01-31
2022-01-01, 2022-02-01
2022-01-01, 2023-01-01

 

[FahadTiger]

Will this work?

no...
View attachment 107113

How about this one?

SELECT Table1.id, Table1.date1, Table1.date2, DateDiff("m",[date1],[date2])*30+(Day([date2])-Day([date1])) AS dT
FROM Table1;

perfect...Thank you very much..much better than my beginner method

 

[FahadTiger]

You are right..but my client has a lot of workers and he wants to count 30 days per month to calculate salaries..this is how the agreement with them..honestly, I did not come across such an agreement

thank you plog​

don't understand, what do you expect from the example you listed