Excel formula per periodo di permanenza

[iki]

forse il titolo è la forma più chiara che sono riuscito a produrre.

mi serve una formula che mi calcoli i giorni di permanenza annuale in una struttura.

riesco a far calcolare la permanenza totale con la differenza tra date, ma il discorso mi si complica quando gli ingressi o le uscite sono antecedenti o postdatati rispetto all'anno in corso.
cerco di fare un esempio

[*] Anno 2010


nome_______nascita ______________ingresso _______uscita ______________Periodo tot _______Periodo Annuale

Tizio_______01/01/1991 _______05/05/2010 _______30/12/2010
Caio _______02/02/1992_______10/10/2009_______10/11/2010
Sempronio ___03/03/1993 _______10/10/2008 _______10/05/2011[*]

[non so come si fa a incorporare una pagine di excel o un file per spiegarmi meglio :-( ]

nel caso di Caio e Sempronio mi servirebbe una formula che automaticamente conteggi solo i giorni di permanenza nell'anno 2010.

tutto ciò sarebbe possibile farlo senza creare una Macro con una formula unica?

[CANAPONE]

Ciao,

nella colonna C gli ingressi nella colonna D le uscite.


=SE(ANNO(C2)<=2010;DATA.DIFF(MAX("01/01/2010";C2);MIN("31/12/2010";D2);"D");0)

Spero sia d'aiuto

[iki]

Ciao,

nella colonna C gli ingressi nella colonna D le uscite.


=SE(ANNO(C2)<=2010;DATA.DIFF(MAX("01/01/2010";C2);MIN("31/12/2010";D2);"D");0)

Spero sia d'aiuto

PERFETTA, GRAZIE MILLE ! ! ! !

una curiosità l'ultima "D" io l'ho lasciata come tale, cosa sta ad indicare all'interno della formula??

[CANAPONE]

Ciao,

DATA.DIFF credo sia una vecchia funzione di Lotus 123 ignorata nel successivo sviluppo di Excel.

La "D" (days) è il tipo di informazione richiesto alla funzione: differenza giorni in questo caso.

La funzione non è documentata su Excel, ti segnalo un paio di indirizzi che chiariscono cosa puoi ottenere dalla funzione.

http://www.confcooperative.it/_vti_bin/ ... atedif.htm

oppure

http://www.informex.it/rarinantes/funzi ... excel.html

Saluti

[Anthony47]

Oppure, andando alla sorgente: http://support.microsoft.com/kb/625201/it

Ciao a tutti!

[iki]

Grazie Mille

[iki]

ed ora una domanda più complicata, sono due ore che cerco una soluzione traendo spunto dalla formula consigliata precedentemente e seguendo i consigli di microsoft, ma non ne vengo a capo.

stesso problema di prima, solo che devo considerare SOLO i giorni lavorativi dal Lunedì al sabato.

ovvero:
1) ho due date e devo calcolare i giorni lavorativi (lun-Sab)
2)calcolare solo i giorni lavorativi (Lun-Sab) però all'interno dell'anno (che sia 2010, 2011 o 2012).


ho usato sul totale i giorni lavorativi proposti dal PC ma conta dal lunedì al venerdì (magari lavorassi solo fino al venerdì)

c'è una soluzione a questo mio cruccio?

[Anthony47]

Per questo userai una formula del tipo

Codice: Seleziona tutto

=SOMMA((GIORNO.SETTIMANA(RIF.RIGA(INDIRETTO(D2&":"&MAX(DATA(ANNO(D2);1;1);C2)));2)<7)*(CONTA.SE(festivi;RIF.RIGA(INDIRETTO(D2&":"&MAX(DATA(ANNO(D2);1;1);C2))))=0))

Da confermare con Contr-Maiusc-Enter, non solo Enter.

Compensa gli eventuali giorni festivi, che immagino non vanno calcolati; ma per questo e' necessario avere l' elenco delle festivita':
-in una colonna a piacere scrivi in verticale tutte le date che corrispondono a festivita'; la data deve essere intera, es 25 apr 2011, non basta il solo 25 Apr.
-alle celle riservate per l' elenco (allungalo pure piu' lungo del necessario, cosi' puoi aggiungere liberamente) assegni il nome "festivi" (Selezioni celle, poi Menu /Inserisci /Nome /Definisci; scrivi il nome "festivi" (senza virgolette), Ok.
-poi usi la formula che ti ho dato (da confermare con Contr-Maiusc-Enter)

Ciao

[iki]

Ciao Anthony47

ho provato a copiare la tua formula ma ho qualche difficoltà nel modificare il nome delle celle.
più precisamente non capisco come modificare correttamente le date:

ipotizzando che le prime due date sono in E3 (inizio presenza) ed in F3 (fine presenza) come vado a sostituirle?
ma soprattutto come faccio a definire come range l'anno (in questo caso mi serve il 2010, ma poi lo farò per tutti gli anni avvenire.

io ho fatto così, cosa ho sbagliato?

Codice: Seleziona tutto

=SOMMA((GIORNO.SETTIMANA(RIF.RIGA(INDIRETTO(E3&":"&MAX(DATA(ANNO(E3);1;1);F3)));2)<7)*(CONTA.SE(festivi;RIF.RIGA(INDIRETTO(E3&":"&MAX(DATA(ANNO(E3);1;1);F3))))=0))

mi fa il conteggio non molto giusto, e fa la somma totale e non quella dei giorni dell'anno interessata.

l'opsite è entrato il 18dicembre2009 ed uscito il 22gennaio2010 e come risultato mi dà 31 mentre dovrebbe essere 29 (se consdiero la permanenza totale) e 17 se considero solo dell'anno che mi interessa (che è la soluzione che vorrei)

grazie mille intanto per la mega formula che mi hai scritto, io adesso proverò a capirci e farla funzionare come mi serve... però hai la soluzione in tasca (le vostre abilità mi guardano dall'alto su questo fronte ) la accolgo volentieri

[iki]

ho corretto il conteggio, esce 27, avevo sbagliato ad indicare i festivi, ma rimane li problema che mi servirebbe un contatore che limita automaticamente la somma all'anno considerato.

[Anthony47]

La formula che ti ho dato calcola la permanenza nell' anno di uscita; quindi preleva l' anno dalla data di uscita.
Ora non mi e' chiaro in effetti tu cosa voglia fare; allora diciamo che se i tuoi dati sono in E3 (inizio presenza) ed in F3 (fine presenza) e se in G3 scrivi l' anno che vuoi rilevare, la formula potrebbe diventare

Codice: Seleziona tutto

=SOMMA((GIORNO.SETTIMANA(RIF.RIGA(INDIRETTO(F3&":"&E3));2)<7)*(CONTA.SE(festivi;RIF.RIGA(INDIRETTO(F3&":"&E3)))=0)*(ANNO(RIF.RIGA(INDIRETTO(F3&":"&E3)))=G3))


Sempre Contr-Maiusc-Enter

Se cosi' non risolvi, allora spiega nuovamente che calcoli vuoi fare

Ciao

[iki]

P E R F E T T A !! !!
per spiegare meglio:

Tizio ha fatto il suo ingresso il 12 dicembre 2009 ed è uscito il 22 gennaio 2010

mi serviva il contatore che però calcolasse solo i giorni da lun a sab ma solo nel 2010, poi mi servirà nel 2011 ( e nella casella da te indicata cambierò semplicemente la data).

il problema mi si poneva anche quando tizio entrava nel 2009 ed è presente tutt'ora, ma a me serve di sapere SOLO i giorni lavorativi del 2010.

GRAZIE MILLE.
ora mi studio la formula e vediamo se imparo qualcosa

[Anthony47]

Ok. Ricorda che "festivi" deve contenere i festivi di tutti gli anni che vai a interrogare, quindi ad esempio 25 apr 2009, 25 apr 2010, 25 apr 2011, etc.

Ciao

[iki]

Grazie mille, sono anche riuscito a sfruttare la formula modificandola per ottenere semplicemente il conteggio dei giorni lavorativi (sempre da lun a sab) che altrimenti con excel non si otteneva.

GRAZIE ANCORA