2nd screen shot
Last Screen shot
Do you want a count or a total?
If you want a total, I suggest modifying the original calculation so that you have one field for the years, one for the months and one for the days. That would Make it easier to compute total years, months and days before doing a conversion to adjust the resulting totals so that you don't see a result that has something like 2 years, 20 months...
Is it possible to do in current format to keep in 1 field?
I guess I need a total to add up all.
It's simpler not to. You can take the three individual values and use a calculation field to combine them to get the text and number combination your current calculation provides. A combination of layout text and merge fields also may be used.
cTenureYear & " years, " & cTenureMonths & " months, and " & cTenureDays & " days"
or as merge fields in your portal row:
<<Table::cTenureYear>> years, <<Table::cTenureMonths>> months, and <<Table::cTenureDays>> days"
I see the merges part and work with that one, but how would I change my calc of date term - date hire to just give me years, and then months and then days in separate fields? Thanks
Make copies of your calculation field and edit each to limit the last line to just y, m and d for each of the three fields. You can also remove the corresponding parts where the removed variables are assigned a value.
I am sorry Phil, I am not understanding your last post.
Go to Manage | Database | Fields. Select the field by clicking it. Click duplicate. Rename the field and click change. Double click the new field or click Options to open the calculation dialog so that you can edit it.
To get a calculation for just the years, change:
Y & " years, " & M & " months, and " & D & " days"
You can also delete the two parts that start with M = and D =.
Repeat for months and days but keep a different part of the expression
I am getting 0 in the years and months fields. Did I get a calc wrong?
I see no reason why you are getting a zero. I went back and found your previous thread so that I could copy the original expression in order to paste it into the Data Viewer for testing.
Let ( [date1 = GetAsDate ( "5/1/99" ) ;
date2 = GetAsDate ( "1/1/2014" ) ;
neg = Case ( date1 > date2 ; -1 ; 1 ) ;
d1 = Case ( neg < 0 ; date2 ; date1 ) ;
d2 = Case ( neg < 0 ; date1 ; date2 ) ;
d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (
Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;
m = mod ( Month ( d2 ) - Month ( d1 ) -
( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;
y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
y & " years, " & m & " months, and " & d & " days" & ¶
Produces a result of:
14 years, 8 months, and 0 days
If I then delete the blue parts, I get: 14
Making similar changes to get just the month result produced: 8
My bad, I was using date that were close together therefore the year and month was "0". That is working in the portal, how would I do the total outside the portal to show me total of years, months and days. I can't do a regular summary total can I since date fields? Thanks