View original (French)

Convert a duration to years, months, and days in numbers.

Solved
cece0403 -  
 cece0403 -
Hello,

I want to convert service times provided in years, months, and days into a number of days to automatically calculate an average service based on different criteria.
Example:
2 year(s) 1 month 12 days = 773

Configuration: Windows 7 / Internet Explorer 9.0

5 réponses

Réponse 1 / 5
Aubinus Posted messages 94 Status Membre 28
 
Hello, would you like software that automatically calculates 2 years, 1 month, and 12 days?

--
In terms of IT
Aubinus
3
Réponse 2 / 5
Raymond PENTIER Posted messages 58551 Registration date   Status Contributeur Last intervention   17 475
 
A conversion will never be accurate.
It would be wise to work from the start date and the end date, which allows the use of the DATEDIF function that takes into account months of 28, 30, and 31 days, and leap years, in particular...
--
It's great, retirement! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
1
Réponse 3 / 5
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Hello
and your starting data is;
__ where
__in what form
__and what do we do with leap years if they are concerned?
looking forward to hearing from you

--
The quality of the answer mainly depends on the clarity of the question, thank you!
0
Aubinus Posted messages 94 Status Membre 28
 
ok I'm going to try to do that in vbs :-D
0
Réponse 4 / 5
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 
Hello Cece

To extract numbers from a cell, multiply them by the number of days and add them all up, the formula for a service time in B4 is:
=LEFT(B4,FIND(" ",B4)-1)*365 + MID(B4,FIND(")",B4)+2,2)*30 + MID(B4,FIND("j",B4)-3,2)*1

Best regards
Via

--
"Imagination is more important than knowledge." A. Einstein
0
cece0403
 
Hello via55, thank you for these elements. When I enter the entire formula, it doesn't work (#value). However, if I only enter the beginning of the formula, it calculates the years correctly. But if I use the same formula possibly in another column for the months and then for the days, it doesn’t work.
0
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755 > cece0403
 
Hello

It's probably because your original text doesn't exactly match what you indicated in your request
Download a sample of your file on cjoint.com, create a link, copy it, and come back to paste it here in a next message, I will take a look at it

Best regards
Via
0
cece0403 > cece0403
 
It's good via55, it works for the month, I just had to put -3 otherwise when I had 10 or 11 months it would take 0 or 1. So cool. Thank you very much.
0
Réponse 5 / 5
Aubinus Posted messages 94 Status Membre 28
 
```html anj = inputbox ("Combien d'années")
mj = inputbox ("Combien de mois")
jj= inputbox ("Combien jour")
tja = anh*365
tjm = mj*30
tj= jj + tja + tjm
msgbox " " & tj & " "

Met ça dans un bloc note que tu renommes en .vbs.
Pour l'instant il donne un nombre approximatif du temps en jours mais je vais l’améliorer pour qu'il donne un temps dont tu pourras choisir l'unité de mesure (mois, jours, heures, minutes, secondes) et qui sera exact.

--
Informatiquement
Aubinus ```
0