› Sviluppare funzionalita su Microsoft Office con VBA › differenza ore
-
AutoreArticoli
-
ciao a tutti
colgo l'occasione per presentarmi... sono nuovo e da poco, per necessità mi sto avvicinando al vba. programmo da anni con altri linguaggi.arriviamo al titolo della discussione:
ho un calendario su excel dove sotto ogni giorno ci sono 4 celle (2 oraInizio 2 oraFine) che inizialmente volevo gestire con le classiche formule di excel ma mi sono reso contro che sarebbe troppo dispendioso
sono riuscito a capire come prendere i valori all'interno delle celle, "parcheggiarli" in variabili e fare la differenza tra di loro...
a questo punto dovrei fare un semplice If per controllare se l'oraInizio non sia maggiore dell'oraFine e qualora lo fosse aggiungere un giorno, quindi 24ore... ma assegnando ad una variabile x (as data) = "24:00" mi da errore... come posso risolvere???
grazie in anticipoAllegati:
You must be logged in to view attached files.Non ho ancora visionato il file, ma per aggiungere un intervallo data o ora prefissato a un valore data o ora puoi usare la funzione VBA chiamata DateAdd(intervallo, numero, data), ad esempio così:
dim x as date
x = dateadd("d", 14, date)
aggiunge 14 giorni ("days") alla data di oggi (date) e assegna il risultato a x, dimensionata opportunamente come tipo data.
Da oggi restituisce appunto 22/11/2018 🙂
in tanto ti ringrazio della risposta
mi sono andato a spulciare un po dataAdd ma non mi sembra che agisca su orari ma solo su date...
io ho una cosa del genere:inizio 18:00
fine 03:00
se faccio 3 -18 = -15 (quindi solo per gli intervalli che sono a cavallo della mezza notte) a questo punto aggiungerei 24h per avere le 9 ore di lavoro effettuate
Carino il prospetto, mi piace, concettualmente è simile a quello che mi sono fatto io qualche secolo fa (il mio però non usa formule nel foglio ma fa fare tutti i calcoli a VBA).
Ti lascio uno spunto di miglioramento: invece di costringere l'utente a cercare i due punti per digitare gli orari, lascialo libero di digitare in sequenza i numeri senza punteggiatura, il codice provvederà a trasformare l'input in un formato orario da inserire nella cella.
Esempio: se digito semplicemente 755, Excel lo trasforma in 07:55. Il range orario valido e riconosciuto è tra "700" e "2359". Ed è semplice farlo: nell'evento Worksheet_Change del foglio, se il target corrisponde a una cella modificabile, basta l'istruzione
Format(Target, "00:00")
per ottenere il risultato desiderato. Se sono andato troppo avanti dimmeloSeguret wrote:ma non mi sembra che agisca su orari ma solo su date
Bè no:
x = dateadd("h", 2, now)
>> 08/11/2018 20:14:12
aggiunge due ore al'orario attuale.
io ho una cosa del genere:
inizio 18:00
fine 03:00
Ti basta anche una cosa così:
format(range("b6")-range("a6"), "hh:mm")
Se a6 = 8.00 e b6 = 12.00 restituisce 4.00
Con orari a cavallo della mezzanotte la faccenda si complica un pochino ma è risolvibile.
Puoi guardare anche come funziona DateDiff.
Calcoli la differenza tra date con DateDiff esprimendola in minuti (parametro "n").
Se minore di zero lo togli a 1440 (che corrisponde ai minuti di un giorno).
Dividi il risultato per 60, la parte intera sono le ore; il modulo della divisione per 60 sono i minuti.
ODDIOOOOOO
una cosa per volta altrimenti non combino nulla....
allora:per l'"autocompletamento"(se cosi vogliamo chiamarlo) ho letto un po in giro ma non ciò capito un gran che... ma sarebbe davvero interessante la cosa...
mentre per DateDiff ho provato a fare così:Hours = DateDiff("n", oraInizio, oraFine) -> con oraInizio=08:00; oraFine=16:30
Range("m6") = Hours
ma mi restituisce -510
che comunque sia è sbagliato...
perche?tra l’altro vorrei che questa Macro salvasse i vari calcoli. C’è un modo particolare o basta cliccare su salva???
prova questa formula, inserita nella cella"R7"
=SE(B6>A6;B6-A6;1+B6-A6)
o questo codice
Option Explicit Sub calcolaOre() If Range("b6") > Range("A6") Then Range("R5") = Range("b6") - Range("A6") Else Range("R5") = 1 + Range("b6") - Range("A6") End If End Sub
Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
Sempre il mare, uomo libero, amerai!
( Charles Baudelaire )Hours = DateDiff("n", oraInizio, oraFine) -> con oraInizio=08:00; oraFine=16:30
Range("m6") = Hours
ma mi restituisce -510
che comunque sia è sbagliato...
A me dà un risultato corretto, cioè 510 che sono i minuti trascorsi dalle 8 alle 16.30.
Il codice di Albatros funziona bene ma secondo me manca un piccolo pezzo per formatta
re opportunamente il risultato:
Range("R5").NumberFormat = "hh:mm"
Seguret wrote:tra l’altro vorrei che questa Macro salvasse i vari calcoli. C’è un modo particolare o basta cliccare su salva???
cosa intendi? certo, basta cliccare su salva oppure inserire l'istruzione
ThisWorkbook.Save
prima di uscire dal codice.
ok problema #1 risolto:
ora anche a me viene...
sono qui:Sub calcolaOre() 'ActiveSheet.Cells(6, 2).Select riga, colonna Dim orarioLav As Date Dim oraFine As Date Dim oraInizio As Date Dim x As String Dim Hours As Long Dim ore As Integer Dim minuti As Integer orarioLav = "08:00" oraFine = ActiveSheet.Cells(6, 2) oraInizio = ActiveSheet.Cells(6, 1) Hours = DateDiff("n", oraInizio, oraFine) If (Hours < 0) Then ore = (Hours + 1440) / 60 minuti = ore Mod 60 Else ore = Hours / 60 minuti = ore Mod 60 End If x = ore & ":" & minuti Range("m6") = x ' x = oraFine - oraInizio ' Range("l6") = x ThisWorkbook.Save End Sub
quindi dovrebbe venire, credo...
il problema è ora che x = ore & ":" & minuti evidentemente non si può fare perché mi restituisce 0,42...ah fammi capire quanti cicli devo fare per calcolare tutti i giorni dell'anno?!?!?!?🤦♂️
Hai dichiarato la variabile "x" as string e comunque non ti serve perchè poi impostare il range direttamente.
Seguret wrote:quanti cicli devo fare per calcolare tutti i giorni dell'anno
Secondo me è un ciclo solo, con un contatore che valuta tutte le coppie di celle interessate.
Asp spiegami questa cosa del Range perché nella guida che sto seguendo ancora non me ne ha parlato è quello che ho fatto fin ora sono andato per intuito
Niente di drammatico 🙂
Puoi fare semplicemente questo, assegnando al range il valore di un'espressione, senza necessariamente memorizzarla in una variabile:
Range("M6") = CDate(ore & ":" & minuti)
La converto con CDate in una data così nella cella di destinazione assumerà il formato giusto (non sempre è così ma insomma, dovrebbe)
infatti non funziona...
mettendo 23:00 e 08:30 mi da 09:09 impostando il formato della cella... altrimenti mi dava 0,42C'era un 'imprecisione nella formula di calcolo del recupero di ore e minuti.
If (Hours < 0) Then ore = Int((Hours + 1440) \ 60) minuti = Int((Hours + 1440)) Mod 60 Else ore = Hours \ 60 minuti = Hours Mod 60 End If Range("m6") = CDate(ore & ":" & minuti) Range("m6").NumberFormat = "hh:mm"
ah ok...
stavo pensando a come far ripetere il calcolo per tutto il calendario e pensavo di usare 2 For, quindi uno per la prima riga e uno per la seconda poi scendo di settimana e riinizio da capo...ho anche notato che a differenza di altri linguaggi in VBA il primo elemento è 1 e no 0, confermi??
comunque l'idea era:
For i = 1 To 14 oraInizio = ActiveSheet.Cells(6, i) oraFine = ActiveSheet.Cells(6, i + 1) Hours = DateDiff("n", oraInizio, oraFine) If (Hours < 0) Then ore = Int((Hours + 1440) \ 60) minuti = Int((Hours + 1440)) Mod 60 Else ore = Hours \ 60 minuti = Hours Mod 60 End If Range("O6") = CDate(ore & ":" & minuti) - orarioLav Range("O6").NumberFormat = "hh:mm" Next i ThisWorkbook.Save End Sub
poi utilizzerei j per scendere di riga, anche se già mi rendo conto che ci possono essere dei problemi
tra l'altro mi sono accorte che se metto 08:00 e 12:00 ovviamente mi va in negativo perché faccio -08:00(orarioLav), ma non stampa le ore in meno fatte ma -1,6666667
Seguret wrote: a differenza di altri linguaggi in VBA il primo elemento è 1 e no 0, confermi??
No
Gli array sono indicizzati a base zero, a meno che non si specifici la direttiva Option Base 1.
Sono i range di Excel che sono un po' diversi perchè non sono proprio degli array.
Va bene i due For (ne va bene anche uno ma la cosa si complica un po' ^_^), studia su carta l'algoritmo e poi stendilo. Poi fai vedere così aggiustiamo il tiro 🙂
<em class="bbp-the-quote-cite">vecchio frac wrote:</em><em class="bbp-the-quote-cite">Va bene i due For (ne va bene anche uno ma la cosa si complica un po' ^_^), studia su carta l'algoritmo e poi stendilo. Poi fai vedere così aggiustiamo il tiro </em>
intanto grazie della disponibilità... mi stai dando una grande mano...
allora sto provando con una Sub di prova:
Sub prova() Dim colStart As Integer Dim colMax As Integer Dim rowStart As Integer Dim rowMax As Integer Dim i As Integer Dim j As Integer 'calcolo l'ultima riga Dim ultimaRigaX As Integer ultimaRigaX = Sheets("Foglio1").Range("A65000").End(xlUp).Row rowStart = 4 colMax = 14 ' colonne massime per settimana rowMax = 19 ' righe massime per mese 'ActiveSheet.Cells(rowStart, colStart).Select rowStart = rowStart + 2 ' salto la riga del mese e della prima settimana Do While x <= rowMax For j = rowStart To rowStart + 1 For i = 1 To colMax Cells(j, i) = "ciao" 'da sostituire con tutto il calcolo delle ore... Next i Next j rowStart = rowStart + 2 Loop End Sub
i due for funzionano riesco a stampare nelle prime 14 caselle la stringa "ciao"
il problema si pone nel momento in cui devo farlo per tutto il calendario; io ripeterei i due For fino all'ultima riga considerando che ogni mese ha 19 righe e poi saltarne 2 per passare all'altro mese... che ne dici?!?!?
Seguret wrote:Do While x <= rowMaxFor j = rowStart To rowStart + 1 For i = 1 To colMax Cells(j, i) = "ciao" 'da sostituire con tutto il calcolo delle ore... Next iNext jrowStart = rowStart + 2Loop
Secondo me , non esci piu da questo loop, perchè x che valore ha?
Qual è il punto di avere gusti diversi, se non mostrare che i cervelli lavorano diversamente, che pensiamo diversamente? ( Alan Turing)
Sempre il mare, uomo libero, amerai!
( Charles Baudelaire )<em class="bbp-the-quote-cite">albatros54 wrote:</em>
<em class="bbp-the-quote-cite">Seguret wrote:</em>Do While x <= rowMaxFor j = rowStart To rowStart + 1 For i = 1 To colMax Cells(j, i) = "ciao" 'da sostituire con tutto il calcolo delle ore... Next iNext jrowStart = rowStart + 2Loop
hai ragione...
Sub prova() Dim colStart As Integer Dim colMax As Integer Dim rowStart As Integer Dim rowMax As Integer Dim i As Integer Dim j As Integer Dim ultimaRigaX As Integer ultimaRigaX = Sheets("Foglio1").Range("A65000").End(xlUp).Row rowStart = 4 colMax = 14 ' colonne massime per settimana rowMax = 19 ' righe massime per mese 'ActiveSheet.Cells(rowStart, colStart).Select rowStart = rowStart + 2 ' salto la riga del mese e della prima settimana MsgBox (rowStart) Do While x <= rowMax For j = rowStart To rowStart + 1 For i = 1 To colMax 'Cells(j, i) = "ciao" 'da sostituire con tutto il calcolo delle ore... Next i Next j rowStart = rowStart + 2 MsgBox (rowStart) x = x + 2 Loop End Sub
cosi però mi stampa su tutte le righe senza saltare quelle dei giorni...
ma non c'è un modo per assegnare a x celle un'etichetta/nome e poi utilizzare quello con un controllo...
cioè, le chiamo CelleOrario e poi faccio un If Cells(x,y) != celleOrario incrementa la riga di uno... o una cosa del genere...Seguret wrote:cioè, le chiamo CelleOrario e poi faccio un If Cells(x,y) != celleOrario
Eh, non è mica python 🙂
Devo recuperare il tuo file che ho perso nel frattempo e ragionare sulla soluzione migliore e più comprensibile. Ma di base quello che hai realizzato è corretto.
Dunque riepilogo: la formula che fa il calcolo delle ore si applica alla singola riga settimanale.
In base al tuo file: la cella O6 deve avere una formula che calcola le ore lavorate in base agli orari di (A6, B6, A7, B7) più (C6, D6, C7, D7) più eccetera fin a M:N.
Dovremo perciò costruire una function personalizzata da usare come formula al posto della formula(ccia) che vedo in O6.
Oppure, in alternativa (come hai fatto tu): dobbiamo costruire una subroutine che effettua il calcolo delle ore passando in rassegna tutte le medesime celle sopracitate (A:N, righe 6 e 7).
Correct? 🙂
C'è un errore nel calendario di gennaio... la giornata "06" è ripetuta due volte
Purtroppo tutto il calendario risente di questo errore 🙁
-
AutoreArticoli