[VBA Excel] Differenza tra due date

[fpucci]

Ho la necessità di calcolare la differenza di due date (nel formato DD/MM/YYYY HH:MM AM) poste su due celle differenti (contigue)

Se in A2 ho: "01/06/2004 09:00 AM" e in B2 ho "02/06/2004 06:00 PM", la differenza mi torna in C2 come "09:00" (considerando che C2 è una cella con il formato "h:mm").

Il conto non mi torna perché dovrebbero essere passate 33 ore effettive, e mi aspettavo proprio tale numero, invece mi visualizza "09:00". Dove sta l'inghippo?

Il secondo quesito è: se volessi calcolare le "ore lavorative" trascorse (considerando che una giornata lavorativa è di 8 ore), basta dividere il risultato per 8? (insomma, calcolare il modulo 8)?

Help, grazie

[fpucci]

Forse ho capito cosa ha combinato quel mattacchione di excel

Ha sottratto la data con la data (il cui risultato diventa "01/01/1900", che però non si vede a causa della formattazione della cella, ma è così) e l'ora con l'ora (il cui risultato fa appunto "09:00").

Ma come faccio a fargli fare quello che vorrei io?

[guldo76]

Sulla differenza tra le date, non credo che excel abbia fatto come dici tu. Il tempo e` rappresentato da un numero seriale, in cui la parte intera misura i giorni e la parte decimale le frazioni di giorno, cioe` ore, minuti e secondi. Credo che il problema possa essere nel formato della cella; hai provato con "[h]:mm"?

Per le ore lavorative mi sa che e` un po' complicato... Immagino che le ore lavorative si contino solo nei giorni lavorativi, no? Allora penso che sarebbe il caso di trovare i giorni lavorativi trascorsi DOPO il primo e PRIMA dell'ultimo giorno considerati, moltiplicare per 8, e poi aggiungerci le ore lavorative effettive del primo giorno e dell'ultimo giorno, che saranno non piu` di 8 per ciascuno.

[fpucci]

Originariamente inviato da guldo76
Sulla differenza tra le date, non credo che excel abbia fatto come dici tu. Il tempo e` rappresentato da un numero seriale, in cui la parte intera misura i giorni e la parte decimale le frazioni di giorno, cioe` ore, minuti e secondi. Credo che il problema possa essere nel formato della cella; hai provato con "[h]:mm"?

Si, so come excel tratta i valori temporali ed è per questo che ho detto quanto riportato nel msg precedente.
Inoltre confermo che la formattazione della cella è proprio quella che hai consigliato proprio tu. Ma come faccio a far visualizzare correttamente la differenza?

[...]sarebbe il caso di trovare i giorni lavorativi trascorsi DOPO il primo e PRIMA dell'ultimo giorno considerati, moltiplicare per 8, e poi aggiungerci le ore lavorative effettive del primo giorno e dell'ultimo giorno, che saranno non piu` di 8 per ciascuno.

Si, mi sembra una buona idea, per cominciare....

[Mixmar]

Un consiglio: forse può essere utile utilizzare la funzione GIORNI.LAVORATIVI.TOT() di Excel, che fa la differenza tra due date calcolando i giorni lavorativi intercorsi...

[fpucci]

Originariamente inviato da Mixmar
Un consiglio: forse può essere utile utilizzare la funzione GIORNI.LAVORATIVI.TOT() di Excel, che fa la differenza tra due date calcolando i giorni lavorativi intercorsi...

Davvero esiste tale funziona? Io ho la versione in inglese di Exce. Come potrebbe chiamarsi una funzione simile?

Ad ogni modo, grazie della dritta

[fpucci]

Originariamente inviato da Mixmar
Un consiglio: forse può essere utile utilizzare la funzione GIORNI.LAVORATIVI.TOT() di Excel, che fa la differenza tra due date calcolando i giorni lavorativi intercorsi...

Nella versione in inglese, la funzione da te descritta si chiama NETWORKDAYS;

Se scrivo:

Worksheets(1).Cells(Riga, Colonna + 1).Value = Networkdays(Worksheets(1).Cells(Riga, T2Col).Value, Worksheets(1).Cells(Riga, T1Col).Value)

mi va in errore eseguendo il codice dicendo che NETWORKDAYS non è definita. Eppure essa è descritta nell'help di excel.

Dove è l'inghippo?
Scusate, ma sono neofita di VBA...

[Mixmar]

Forse non hai installato gli Strumenti di analisi di Excel: nella versione italiana, dal menù Strumenti si seleziona Componenti aggiuntivi e poi Strumenti di analisi (e magari Strumenti di analisi per VBA) e poi si fa click su OK. Così dovrebbe funzionare...

[fpucci]

Originariamente inviato da Mixmar
Forse non hai installato gli Strumenti di analisi di Excel: nella versione italiana, dal menù Strumenti si seleziona Componenti aggiuntivi e poi Strumenti di analisi (e magari Strumenti di analisi per VBA) e poi si fa click su OK. Così dovrebbe funzionare...

Purtroppo sembrano installati.
Quando ho installato questi strumenti di analisi (anche qello per VBA), per sicurezza ho chiuso Excel e l'ho riavviato, ma la sostanza non è cambiata. E comunque, quei due tool risultano spuntati (ossia, presenti)

[guldo76]

Originariamente inviato da fpucci
Se scrivo:

Worksheets(1).Cells(Riga, Colonna + 1).Value = _
Networkdays(Worksheets(1).Cells(Riga, T2Col).Value, _
Worksheets(1).Cells(Riga, T1Col).Value)

mi va in errore eseguendo il codice dicendo che NETWORKDAYS non è definita.

Mi sa che non ho capito bene cosa vuoi fare di preciso; se non sbaglio tu vorresti con quel codice ottenere una cella X1 (supponiamo) col valore:
=NETWORKDAYS(......)
E` cosi`? allora la sintassi e` un pochino piu` complicata:

Worksheets(1).Cells(Riga, Colonna + 1).Value = _
"=Networkdays(" & Worksheets(1).Cells(Riga, T2Col).Value _
& ", " & Worksheets(1).Cells(Riga, T1Col).Value & ")"

[Mixmar]

Forse occorre usare l'oggetto WorksheetFunctions: una cosa tipo:

nomeFoglio.WorksheetFunctions.NetWorkDays(...)

per invocare le funzioni Excel...

Però non so, non l'ho mai usato...

[guldo76]

Originariamente inviato da Mixmar
Forse occorre usare l'oggetto WorksheetFunctions: una cosa tipo:
nomeFoglio.WorksheetFunctions.NetWorkDays(...)
per invocare le funzioni Excel...
Però non so, non l'ho mai usato...

Si, si, c'e` anche quello, e c'e` anche
Application.WorkbookFunctions.NetWorkDays(...)
probabilmente; pero` questi servono per ottenere il risultato direttamente, non per inserire nella cella la formula.
Dipende cosa ti serve.

[fpucci]

Grazie per il supporto

Allora vado con ordine:

Guido76 wrote
Mi sa che non ho capito bene cosa vuoi fare di preciso; se non sbaglio tu vorresti con quel codice ottenere una cella X1 (supponiamo) col valore[...]:

Si, hai compreso bene le mie intenzioni; devo scrivere in una cella il risultato della differenza di due date che si trovano in altre due celle.
Ho applicato la formula da te consigliata, ma mi ha restituito il seguente messaggio di errore:
"Application-defined or object-defined error"
E non so cosa fare.

Spiego meglio cosa voglio ottenere.
Il foglio viene generato da un'applicazione esterna nel formato CSV ed importato con excel. In questo file di input ho le data e le ore in due colonne differenti. Tutte le colonne sono importate nel formato "general" dal wizard.

Con una prima macro (in VBA) sono riuscito a compattare tutte le colonne che hanno data e ora separati, in colonne con data e ora unificate, il cui formato è ora "d/m/yyyy h:mm AM/PM"

Adesso ho il problema di calcolare la differenza tra due colonne il cui formato è custom "dd/mm/yyyy h:mm AM".
Presuppongo quindi che questo formato NON sia TEXT.

Mixmar wrote
Forse occorre usare l'oggetto WorksheetFunctions: una cosa tipo:
nomeFoglio.WorksheetFunctions.NetWorkDays(...)
per invocare le funzioni Excel...

Ho provato anche questa, ma mi da sempre errore nella riga che ho postato in precedenza.
Forse, che sia sbagliata la formattazione delle celle?

[guldo76]

Ho sbagliato io, WorkbookFunctions non esiste.
Cmq, senza usare networkdays, questa potrebbe essere un buon inizio:

sub OreLavorative()
	dim i, j as integer
	dim OraInizio, OraFine as double
	dim Giorni, PrimoGiorno, UltimoGiorno as integer
	dim DurataPrimoGiorno, DurataUltimoGiorno as double
	
	Giorni = 0
	OraInizio = 9/24
	OraFine = 17/24
	j = activesheet.cells(1,1).currentregion.columns.count + 1
	for i = 1 to activesheet.cells(1,1).currentregion.rows.count
		PrimoGiorno = int(cells(i,1))
		UltimoGiorno = int(cells(i,2))
		DurataPrimoGiorno = OraFine - (cells(i,1) - int(cells(i,1)))
		DurataUltimoGiorno = (cells(i,2) - int(cells(i,2))) - OraInizio
		'giorni lavorativi
		PrimoGiorno = PrimoGiorno + 1
		do while PrimoGiorno < UltimoGiorno
			if not (weekday(PrimoGiorno) = vbsaturday or weekday(PrimoGiorno) = vbsunday)
			then
				Giorni = Giorni + 1
			end if
			PrimoGiorno = PrimoGiorno + 1
		loop
	'ore totali
	cells(i,j) = Giorni * 8 + DurataPrimoGiorno + DurataUltimoGiorno
	next i
end sub

Ma considera che[list=1][*]non l'ho testata, quindi provala prima di fidarti[*]ho dato per scontato che i dati comincino dalla cella a1[*]ho dato per scontato che data e ora iniziali siano nella prima colonna, quelle finali nella seconda[*]ho dato per scontato che gli orari fossero sempre compresi tra le 9 e le 17[*]ho ignorato le vacanze, come 1 maggio, 15 agosto, etc... ce le dovrai aggiungere nelle condizioni del IF[*]spero di non aver sbagliato niente [/list=1]

Guldo

[fpucci]

Originariamente inviato da guldo76
Ho sbagliato io, WorkbookFunctions non esiste.

No problem. Io ho usato la WorksheetFunctions

[...]
Ma considera che[list=1][*]non l'ho testata, quindi provala prima di fidarti[*]ho dato per scontato che i dati comincino dalla cella a1[*]ho dato per scontato che data e ora iniziali siano nella prima colonna, quelle finali nella seconda[*]ho dato per scontato che gli orari fossero sempre compresi tra le 9 e le 17[*]ho ignorato le vacanze, come 1 maggio, 15 agosto, etc... ce le dovrai aggiungere nelle condizioni del IF[*]spero di non aver sbagliato niente [/list=1]

Caro Guido76,
che dire? Senz'altro ti dico grazie per il tempo che mi hai dedicato, indipendentemente dal fatto che essa funzioni o no.
Ormai è tardi, ma senz'altro prima me la studio, poi domani cerco di applicarla adattandola al mio foglio.
Tnx

[guldo76]

Prego, di niente, ma ho dimenticato una cosa essenziale: non mi chiamo Guido. Il mio nick e` GULDO.

[fpucci]

Originariamente inviato da guldo76
Prego, di niente, ma ho dimenticato una cosa essenziale: non mi chiamo Guido. Il mio nick e` GULDO.

Azz.... ho pure il "Copy&Paste" bacato

Cmq grazie lo stesso, guldo76

[Mixmar]

Guldo della squadra Ginew!