Discussion:
Excel - sumowanie czasu
(Wiadomość utworzona zbyt dawno temu. Odpowiedź niemożliwa.)
Alecs
2004-08-18 18:51:15 UTC
Witam ponownie,

mam kolejny problem z Excelem. Mam nadzieje, ze i tym razem znajdzie sie
ktos, kto zechce mi pomoc.
Chodzi mi konkretnie o sumowanie czasu pracy. Tabela w excelu ma np. postac

poczatek prac 18.08.2004 8:00
zmiana operacji 18.08.2004 11:30
zmiana operacji 18.08.2004 13:50
koniec pracy 18.08.2004 16:00

Arkusz dla jednego pracownika zawiera wszystkie dni pracy w miesiacu. Czyli
powyzszy tabela przedstawia tylko powtazalny fragment arkusza, odpowiadajacy
tylko jednemu dniu.
Chcialabym, aby program sam znajdowal poczatek i koniec pracy we wszystkie
dni (czyli na calum arkuszu), przy zalozeniu, ze zmian operacji w ciagu dnia
moze byc rozna ilosc, czyli od 1 w zwyz.
I jeszcze jedna sprawa: format czasu zaprezentowany powyzej (przekopiowuje z
systemu rejestrujacego czas pracy) jest nie akceptowany przez Excel. Jak
zmusic Excel do rozpoznawanie w nic czasu, ewentualnie jak zmienic
automatycznie w calym arkuszu format czasu i daty na akceptowany przez Excel

Z gory bede wdzieczna za pomoc
Pozdrawiam
alecs
Stanislaw
2004-08-20 07:12:19 UTC
Post by Alecs
Chodzi mi konkretnie o sumowanie czasu pracy. Tabela w excelu ma np. postac
poczatek prac 18.08.2004 8:00
zmiana operacji 18.08.2004 11:30
zmiana operacji 18.08.2004 13:50
koniec pracy 18.08.2004 16:00
Arkusz dla jednego pracownika zawiera wszystkie dni pracy w miesiacu. Czyli
powyzszy tabela przedstawia tylko powtazalny fragment arkusza, odpowiadajacy
tylko jednemu dniu.
Chcialabym, aby program sam znajdowal poczatek i koniec pracy we wszystkie
dni (czyli na calum arkuszu), przy zalozeniu, ze zmian operacji w ciagu dnia
moze byc rozna ilosc, czyli od 1 w zwyz.
Nie bardzo rozumiem w czym problem.
Na czym ma polegac to znajdowanie poczatku i konca dnia pracy.
Mozesz to opisac np. przez podanie przykladu?

Bo w tym przykladzie czlowiek od razu widzi:
poczatek prac 18.08.2004 8:00
koniec pracy 18.08.2004 16:00
Post by Alecs
I jeszcze jedna sprawa: format czasu zaprezentowany powyzej (przekopiowuje z
systemu rejestrujacego czas pracy) jest nie akceptowany przez Excel. Jak
zmusic Excel do rozpoznawanie w nic czasu, ewentualnie jak zmienic
automatycznie w calym arkuszu format czasu i daty na akceptowany przez Excel
Na poczatek zamiana istniejacego formatu daty i czasu
na format zrozumialy przez program Excel

- problem nie jest jednak prosty,
bo (jak mozna zauwazyc)
zapis czasu moze wystapic jako
g:mm lub gg:mm
czyli godzina moze byc zapisywana za pomoca jednej lub dwoch cyfr
co komplikuje sprawe,
a przypuszczam, ze i dzien w dacie moze tez byc zapisywany roznie,
za pomoca jednej lub dwoch cyfr.

Uczynie na razie pierwszy krok
- oto formula zamieniajaca zapis 18.08.2004 13:50
(przyjmuje, ze znajduje sie on w komorce A1)
na format czasu zrozumialy przez Excela

=DATA(FRAGMENT.TEKSTU(A1;7;4);
FRAGMENT.TEKSTU(A1;4;2);
FRAGMENT.TEKSTU(A1;1;2))
+CZAS(FRAGMENT.TEKSTU(A1;12;2);
FRAGMENT.TEKSTU(A1;15;2);0)

Ale uwaga, formula bedzie dzialac poprawnie jedynie wtedy,
gdy zapis dnia i godziny bedzie za pomoca dwoch cyfr.


pzdr
Stanislaw
--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl
mk
2004-08-20 07:46:13 UTC
Post by Stanislaw
Uczynie na razie pierwszy krok
- oto formula zamieniajaca zapis 18.08.2004 13:50
(przyjmuje, ze znajduje sie on w komorce A1)
na format czasu zrozumialy przez Excela
=DATA(FRAGMENT.TEKSTU(A1;7;4);
FRAGMENT.TEKSTU(A1;4;2);
FRAGMENT.TEKSTU(A1;1;2))
+CZAS(FRAGMENT.TEKSTU(A1;12;2);
FRAGMENT.TEKSTU(A1;15;2);0)
A ja uczyniê drugi krok :-):
Formu³a, je¿eli nigdzie siê nie pomyli³em uwzglêdnia fakt, ¿e dni, misi±ce i
godziny mog± byæ jedno lub dwucyfrowe:

=DATA(FRAGMENT.TEKSTU(A1;ZNAJD¬(".";A1;ZNAJD¬(".";A1)+1)+1;4);FRAGMENT.TEKST
U(A1;ZNAJD¬(".";A1)+1;
ZNAJD¬(".";A1;ZNAJD¬(".";A1)+1)-ZNAJD¬(".";A1)-1);LEWY(A1;ZNAJD¬(".";A1)-1))
+CZAS(FRAGMENT.TEKSTU(A1;ZNAJD¬(" ";A1)+
1;ZNAJD¬(":";A1)-(ZNAJD¬(" ";A1)+1));PRAWY(A1;D£(A1)-ZNAJD¬(":";A1));0)
--
Marcin
-------------------------------------
wywal 'bleble' z adresu mailowego
http://excel.republika.pl
-------------------------------------
s***@op.pl
2004-08-20 09:15:19 UTC
Post by Stanislaw
Uczynie na razie pierwszy krok
 - oto formula zamieniajaca zapis 18.08.2004 13:50
(przyjmuje, ze znajduje sie on w komorce A1)
na format czasu zrozumialy przez Excela
=DATA(FRAGMENT.TEKSTU(A1;7;4);
      FRAGMENT.TEKSTU(A1;4;2);
      FRAGMENT.TEKSTU(A1;1;2))
+CZAS(FRAGMENT.TEKSTU(A1;12;2);
      FRAGMENT.TEKSTU(A1;15;2);0)
Formuła, jeżeli nigdzie się nie pomyliłem uwzględnia fakt, że dni, misiące i
=DATA(FRAGMENT.TEKSTU(A1;ZNAJDŹ(".";A1;ZNAJDŹ(".";A1)+1)+1;4);FRAGMENT.TEKST
U(A1;ZNAJDŹ(".";A1)+1;
ZNAJDŹ(".";A1;ZNAJDŹ(".";A1)+1)-ZNAJDŹ(".";A1)-1);LEWY(A1;ZNAJDŹ(".";A1)-1))
+CZAS(FRAGMENT.TEKSTU(A1;ZNAJDŹ(" ";A1)+
1;ZNAJDŹ(":";A1)-(ZNAJDŹ(" ";A1)+1));PRAWY(A1;DŁ(A1)-ZNAJDŹ(":";A1));0)
--
Marcin
Pora teraz zautomatyzowac konwertowanie dat przy pomocy VBA.
Daty przed konwersja sa traktowane przez Excela
jak zwykly tekst.
Natomiast po skonwertowaniu program traktuje je jako liczby
sformatowane jako daty,
mozna juz wtedy wykonywac na nich operacje artmetyczne.

Ponizsza procedura VBA dokonuje tej niewątpliwie zmudnej operacji
w calym zaznaczonym obszarze komorek.

Jednak - dla bezpieczenstwa danych -
najlepiej uruchomic procedure na kopii oryginalnych danych.

Sub Konwertuj_date()

Dim komorka As Range

For Each komorka In Selection
' ilosc dni w dacie = 1, kropka na drugim miejscu
' oraz ilosc znakow w czasie = 4, "g:mm"
If InStr(1, komorka.Value, ".") = 2 And _
Len(komorka.Value) - InStr(1, komorka.Value, " ") = 4 Then
With komorka
.Value = DateSerial(Mid(.Value, 6, 4), _
Mid(.Value, 3, 2), _
Mid(.Value, 1, 1)) _
+ TimeSerial(Mid(.Value, 11, 1), _
Mid(.Value, 13, 2), 0)
.NumberFormat = "d.mm.yyyy h:mm"
End With

' ilosc dni w dacie = 1, kropka na drugim miejscu
' oraz ilosc znakow w czasie = 5, "gg:mm"
ElseIf InStr(1, komorka.Value, ".") = 2 And _
Len(komorka.Value) - InStr(1, komorka.Value, " ") = 5 Then
With komorka
.Value = DateSerial(Mid(.Value, 6, 4), _
Mid(.Value, 3, 2), _
Mid(.Value, 1, 1)) _
+ TimeSerial(Mid(.Value, 11, 2), _
Mid(.Value, 14, 2), 0)
.NumberFormat = "d.mm.yyyy h:mm"
End With

' ilosc dni w dacie = 2, kropka na trzecim miejscu
' oraz ilosc znakow w czasie = 4, "g:mm"
ElseIf InStr(1, komorka.Value, ".") = 3 And _
Len(komorka.Value) - InStr(1, komorka.Value, " ") = 4 Then
With komorka
.Value = DateSerial(Mid(.Value, 7, 4), _
Mid(.Value, 4, 2), _
Mid(.Value, 1, 2)) _
+ TimeSerial(Mid(.Value, 12, 1), _
Mid(.Value, 14, 2), 0)
.NumberFormat = "d.mm.yyyy h:mm"
End With

' ilosc dni w dacie = 2, kropka na trzecim miejscu
' oraz ilosc znakow w czasie = 5, "gg:mm"
ElseIf InStr(1, komorka.Value, ".") = 3 And _
Len(komorka.Value) - InStr(1, komorka.Value, " ") = 5 Then
With komorka
.Value = DateSerial(Mid(.Value, 7, 4), _
Mid(.Value, 4, 2), _
Mid(.Value, 1, 2)) _
+ TimeSerial(Mid(.Value, 12, 2), _
Mid(.Value, 15, 2), 0)
.NumberFormat = "d.mm.yyyy h:mm"
End With
End If
Next komorka

End Sub


pzdr
Stanislaw
--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl
Tajan
2004-08-20 11:18:41 UTC
Witam!

(...)
Ponizsza procedura VBA dokonuje tej niew±tpliwie zmudnej operacji
w calym zaznaczonym obszarze komorek.
Jednak - dla bezpieczenstwa danych -
najlepiej uruchomic procedure na kopii oryginalnych danych.
Sub Konwertuj_date()
(.....)

A ja wymyslilem cos takiego:

Sub Konwertuj_date()

Dim tekst As String
Dim kom As Range
Dim data_czas, data, czas

For Each kom In Selection
With kom
tekst = Application.WorksheetFunction.Trim(.Value)
If tekst Like "*#.*#.#### *#:##" Then
data_czas = Split97(tekst, " ")
data = Split97(data_czas(1), ".")
czas = Split97(data_czas(2), ":")
.Value = DateSerial(data(3), data(2), data(1)) + _
TimeSerial(czas(1), czas(2), 0)
.NumberFormat = "d.mm.yyy h:m"
End If
End With
Next
End Sub


Function Split97(sStr, sDelim) As Variant

Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sDelim, """,""") & """}")
End Function


Wydaje mi sie, ze jest prostsze i bardziej odporne na ewentualne roznice
zapisow.
Uwaga! Funkcja Split97 jest wymagana tylko dla Exce'a 97. W wyzszych
wersjach mozna ja zastapic wbudowana funkcja Split.

Pozdrawiam
Tajan
Alecs
2004-08-21 08:52:23 UTC
Witam!

dziekuje za wszystkie odpowiedzi. Dzisiaj zabiore sie za ich testowanie'

Odpowiadam na pytanie Stanislawa
Arkusz ma postac:
pocz±tek pracy 18.08.2004 8:00
zmiana operacji -----------------
koniec pracy 18.08.2004 16:00

pocz±tek pracy 19.08.2004 22:00
zmiana operacji 20.08.2004 00:01
zmiana operacji ------------------
zmiana operacji ------------------
koniec pracy 20.08.2004 6:00

itd kolejne dni pracy, przy zalozeniu, ze zmian operacji moze byc nie zawsze
ta sama ilosc. Wydaje mi sie, ze to utrudnia sprawe, bo lokalizacja poczatku
i konca pracy na arkuszu jest trudna do okreslenia. Nie wiadomo dokladnie, w
ktorych linijkach bedzie zaczynal sie poczatek i koniec pracy w kolejnych
dniach.

Mam nadzieje, ze opisalam problem w sposob jasny.

Jeszcze raz wielkie dzieki za pomoc

Pozdrawiam
alecs
Post by Tajan
Witam!
(...)
Ponizsza procedura VBA dokonuje tej niew±tpliwie zmudnej operacji
w calym zaznaczonym obszarze komorek.
Jednak - dla bezpieczenstwa danych -
najlepiej uruchomic procedure na kopii oryginalnych danych.
Sub Konwertuj_date()
(.....)
Sub Konwertuj_date()
Dim tekst As String
Dim kom As Range
Dim data_czas, data, czas
For Each kom In Selection
With kom
tekst = Application.WorksheetFunction.Trim(.Value)
If tekst Like "*#.*#.#### *#:##" Then
data_czas = Split97(tekst, " ")
data = Split97(data_czas(1), ".")
czas = Split97(data_czas(2), ":")
.Value = DateSerial(data(3), data(2), data(1)) + _
TimeSerial(czas(1), czas(2), 0)
.NumberFormat = "d.mm.yyy h:m"
End If
End With
Next
End Sub
Function Split97(sStr, sDelim) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sDelim, """,""") & """}")
End Function
Wydaje mi sie, ze jest prostsze i bardziej odporne na ewentualne roznice
zapisow.
Uwaga! Funkcja Split97 jest wymagana tylko dla Exce'a 97. W wyzszych
wersjach mozna ja zastapic wbudowana funkcja Split.
Pozdrawiam
Tajan
Tajan
2004-08-23 06:57:52 UTC
Witam!
Post by Alecs
Odpowiadam na pytanie Stanislawa
pocz±tek pracy 18.08.2004 8:00
zmiana operacji -----------------
koniec pracy 18.08.2004 16:00
pocz±tek pracy 19.08.2004 22:00
zmiana operacji 20.08.2004 00:01
zmiana operacji ------------------
zmiana operacji ------------------
koniec pracy 20.08.2004 6:00
itd kolejne dni pracy, przy zalozeniu, ze zmian operacji moze byc nie zawsze
ta sama ilosc. Wydaje mi sie, ze to utrudnia sprawe, bo lokalizacja poczatku
i konca pracy na arkuszu jest trudna do okreslenia. Nie wiadomo dokladnie, w
ktorych linijkach bedzie zaczynal sie poczatek i koniec pracy w kolejnych
dniach.
Mam nadzieje, ze opisalam problem w sposob jasny.
Jezeli chodzi o sumowanie czasu pracy w miesiacu, to przy zalozeniu, ze juz
doprowadzisz do porzadku format zapisu danych, wystarczy uzyc takiej
formuly:
=SUMA.JE¯ELI(A1:A10;"*koniec pracy*";B1:B10)-SUMA.JE¯ELI(A1:A10;"*pocz±tek
pracy*";B1:B10)
oczywiscie, przy zalozeniu, ze dane znajduja sie w obszarze A1:B10

Powodzenia
Tajan