Sum days_month_years (1 Viewer)

[hatmak]

how to calculate sum for the following
per each employ

if count of days more than 30 than add 1 to month than
if month =12
month =0 and add 1 to years

EMP	EMP	SumOfDAYS_NO	SumOfMONTH_NO	SumOfyears_No			final days	final month	final years
A​	A​	32	11	13			2	0	14
B​	B​	37	17	15			7	11	16

 

[CJ_London]

you normally do this using the datediff function between two dates.

I don't understand your data - what is it supposed to represent?
DATA DATA

EMP	DAYS_NO	MONTH_NO	years_No
A	25​	1​	12​
A	7​	10​	1​

probably easier to sum everything to get a total number of days, then break it back down - something like this

ttlDays: DayNo+(monthno*30)+(yearno*12*30)
years: ttlDays\360
months: ttlDays-(years*360))\30
days: ttldays-(years*360)-(months*30)

 

[hatmak]

thanks for your response CJ_London

this what I want

 

[isladogs]

Its a bit more complicated allowing for actual number of days in each month and year (including leap years)

You could look at my webpage on calculating age. It includes a function to get age in years, months and days

Calculate Age

This article includes several functions to calculate age in years, years & months or years, months and days. It even has a function to calculate age in Years, Months, Days, Hours, Minutes And Seconds!

www.isladogs.co.uk

It would be easy to adapt to using the difference between any two dates

In addition, following a request on LinkedIn, the same page even has a function to get age down to the number of hours, minutes and seconds.
Personally I can't see the point, but the request was genuine!

 

[plog]

I really question the structure of your table. But here's what you need:

FinalDays: TotalDays Mod 31

FinalMonths: (TotalMonths + iif(TotalDays>30, 1, 0)) Mod 12

FinalYears: TotalYears + iif((TotalMonths + iif(TotalDays>30, 1, 0)>11, 1, 0))

Your field names hurt my teeth so I used my own, you'll have to customize it to yours, or better yet, change yours to mine.

 

[plog]

Thinking about this more I see some points of failure:

If TotalDays >= 60 it will still only add just 1 month to TotalMonths
If TotalMonths >= 24 it will still only add just 1 year to FinalYears

Instead of testing if over 30/12 you should divide them by 30/12 and add the integer part of that division to the next unit in line.

 

[isladogs]

Instead of testing if over 30/12 you should divide them by 30/12 and add the integer part of that division to the next unit in line.

But why do approximate calculations based on 12*30 day months = 360 day year when it can be done properly?

 

[arnelgp]

see also 2_final query.

 

[hatmak]

thanks arnelgp