Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Rundungseinstellung
#1
Erstmal hallo zusammen,

ich bin gerade dabei eine Excel Tabelle zu erstellen und habe da nun ein Problem bei den Rundungen der Zahlen.
Und zwar soll die Excel Tabelle alles was 0,5 und drüber ist (0,5;0,6 usw) auf eine ganze Zahl (0,6 -> 1) runden, aber alles was unter 0,5 (0,4;0,3) ist so lassen und gar nicht abrunden.

Aus 0,5 sollte 1 werden und aus 0,4 sollte 0,4 bleiben.

Gibt es dafür eine Einstellungsmöglichkeit?

Und gibt es eine Formel mit dieser ich Zeiträume ausrechnen kann.
Also z.B. 01.01.2015 bis 20.03.2015. Hier bräuchte ich das Ergebnis 2 Monate und 20 Tage. Geht so etwas?
Oder 16.03.2015 bis 31.03.2015 = 16 Tage

Schon mal vielen Dank für eure Hilfe.
to top
#2
Hi,

zu Deiner ersten Frage: Meinst Du so ...

Arbeitsblatt mit dem Namen 'Tabelle1'
 AB
11,11,1
22,22,2
33,33,3
44,44,4
55,56
66,67
77,78
88,89
99,910
101010

ZelleFormel
B1=WENN(A1-GANZZAHL(A1)>=0,5;AUFRUNDEN(A1;0);A1)
B2=WENN(A2-GANZZAHL(A2)>=0,5;AUFRUNDEN(A2;0);A2)
B3=WENN(A3-GANZZAHL(A3)>=0,5;AUFRUNDEN(A3;0);A3)
B4=WENN(A4-GANZZAHL(A4)>=0,5;AUFRUNDEN(A4;0);A4)
B5=WENN(A5-GANZZAHL(A5)>=0,5;AUFRUNDEN(A5;0);A5)
B6=WENN(A6-GANZZAHL(A6)>=0,5;AUFRUNDEN(A6;0);A6)
B7=WENN(A7-GANZZAHL(A7)>=0,5;AUFRUNDEN(A7;0);A7)
B8=WENN(A8-GANZZAHL(A8)>=0,5;AUFRUNDEN(A8;0);A8)
B9=WENN(A9-GANZZAHL(A9)>=0,5;AUFRUNDEN(A9;0);A9)
B10=WENN(A10-GANZZAHL(A10)>=0,5;AUFRUNDEN(A10;0);A10)

Zur zweiten Frage: Welches Ergebnis erwartest Du für den Zeitraum 15.01.2015 bis 20.03.2015 - 2 Monate und 5 Tage? Oder soll der Januar als voller Monat berechnet werden, also 2 Monate und 20 Tage?
to top
#3
Hallo!

Meinst du es so? incl. Starttag.

Tabelle2

ABC
101.03.201131.03.20111 Monat
201.03.201101.03.20111 Tag
316.11.194017.03.2015892 Monate 2 Tage
401.06.199717.03.2015213 Monate 17 Tage
515.01.201427.11.201410 Monate 13 Tage
615.01.201520.03.20152 Monate 6 Tage
Formeln der Tabelle
ZelleFormel
C1=WENN(DATEDIF(A1;B1+1;"m")=0;"";DATEDIF(A1;B1+1;"m")&" Monat"&WENN(DATEDIF(A1;B1+1;"m")<>1;"e ";" "))&WENN(TAG(B1+1)-TAG(A1)+(TAG(A1)>TAG(B1+1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))=0;"";TAG(B1+1)-TAG(A1)+(TAG(A1)>TAG(B1+1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))&" Tag"&WENN(TAG(B1)-TAG(A1)+1+(TAG(A1)>TAG(B1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))<>1;"e";""))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4







Gruß
Wilfried
to top
#4
Hallo Wilfried,

hast wohl vergessen, die Smilies auszuschalten !
Gruß Conny Smile
_______________________________________________________________

Ich habe keine Lösung, aber ich bewundere dein Problem !
to top
#5
Ja mega... das erste funktioniert super...

Bei der zweiten Frage:
15.01.2015 bis 20.03.2015

Der Hintergrund ist dieser, dass ich ausrechnen will, wieviel Stunden jemand im Monat arbeiten muss.

Deshalb wenn der Vertrag z.B. 15.01. bis 31.01 geht, will ich wissen:
15.01. - 31.01. = ? Tage; (17 Tage)

Händische Formel:
Tage des vollen Monats 31 (da Januar)
Arbeitstage laut Vertrag 17
Wöchentliche Arbeitszeit 10 Stunden

Dann lautet unsere Formel nun so:
4,3482 / 31 * 17 *10= 23,84 = Die Stunden die er im Zeitraum 15.01. - 31.01 erbringen muss.

In unserem Beispiel 15.01.15 - 20.03.15 sollten er nun den Januar, Februar und dann März ausrechnen und zum Schluss die drei Monate zusammen addieren.

Januar: 4,3482 / 31 * 17 *10= 23,84
Februar: 4,3482 / 28 * 28 *10= 43,48
März: 4,3482 / 31 * 20 *10= 28,05

Zeitraum 15.01.15 - 20.03.15 komplett 95,37 Stunden.
to top
#6
(17.03.2015, 10:10)Wilfried Höttl schrieb: Hallo!

Meinst du es so? incl. Starttag.

Tabelle2

ABC
101.03.201131.03.20111 Monat
201.03.201101.03.20111 Tag
316.11.194017.03.2015892 Monate 2 Tage
401.06.199717.03.2015213 Monate 17 Tage
515.01.201427.11.201410 Monate 13 Tage
615.01.201520.03.20152 Monate 6 Tage
Formeln der Tabelle
ZelleFormel
C1=WENN(DATEDIF(A1;B1+1;"m")=0;"";DATEDIF(A1;B1+1;"m")&" Monat"&WENN(DATEDIF(A1;B1+1;"m")<>1;"e ";" "))&WENN(TAG(B1+1)-TAG(A1)+(TAG(A1)>TAG(B1+1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))=0;"";TAG(B1+1)-TAG(A1)+(TAG(A1)>TAG(B1+1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))&" Tag"&WENN(TAG(B1)-TAG(A1)+1+(TAG(A1)>TAG(B1))*TAG(DATUM(JAHR(A1);MONAT(A1)+1;))<>1;"e";""))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4







Gruß
Wilfried

(17.03.2015, 10:18)coemm schrieb: Hallo Wilfried,

hast wohl vergessen, die Smilies auszuschalten !
danke coemm
[-] Folgende(r) 1 Benutzer sagt Danke an Wilfried Höttl für diesen Beitrag:
cloud7
to top
#7
Hi,

Zitat:Bei der zweiten Frage:

15.01.2015 bis 20.03.2015

Der Hintergrund ist dieser, dass ich ausrechnen will, wieviel Stunden jemand im Monat arbeiten muss.

Deshalb wenn der Vertrag z.B. 15.01. bis 31.01 geht, will ich wissen:
15.01. - 31.01. = ? Tage; (17 Tage)


bist du sicher, dass es vom 15.1 bis zum 31.1. 17 Arbeitstage sind? Oder wird bei euch Samstag und Sonntag durchgearbeitet? Ich komme hier auf 12 Tage:

Tabelle1

ABC
115.01.201531.01.201512 Tage
Formeln der Tabelle
ZelleFormel
C1=NETTOARBEITSTAGE(A1;B1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#8
Hallo

wir arbeiten zwar nicht Samstag und Sonntag, aber zum berechnen sind es 17 Tage. ;-)
Arbeitstage ist wohl hierfür der falsche Ausdruck.
to top
#9
Hi,

ich kenne Deinen Tabellenblattaufbau leider nicht, hab aber trotzdem mal was für ein Jahr gebastelt:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKLMNOPQRSTUVWXYZ
115.01.1520.03.15Jan 1517Feb 1528Mrz 1520                  
2 95,373123,842843,483128,05 0 0 0 0 0 0 0 0 0

ZelleFormel
C1=DATUM(JAHR(A1);MONAT(A1)+1;)
D1=MIN(C1;$B1)-A1+1
E1=WENN($B1<=C1;"";C1+32-TAG(C1+32))
F1=WENN(E1="";"";MIN(E1;$B1)-C1)
G1=WENN($B1<=E1;"";E1+32-TAG(E1+32))
H1=WENN(G1="";"";MIN(G1;$B1)-E1)
I1=WENN($B1<=G1;"";G1+32-TAG(G1+32))
J1=WENN(I1="";"";MIN(I1;$B1)-G1)
K1=WENN($B1<=I1;"";I1+32-TAG(I1+32))
L1=WENN(K1="";"";MIN(K1;$B1)-I1)
M1=WENN($B1<=K1;"";K1+32-TAG(K1+32))
N1=WENN(M1="";"";MIN(M1;$B1)-K1)
O1=WENN($B1<=M1;"";M1+32-TAG(M1+32))
P1=WENN(O1="";"";MIN(O1;$B1)-M1)
Q1=WENN($B1<=O1;"";O1+32-TAG(O1+32))
R1=WENN(Q1="";"";MIN(Q1;$B1)-O1)
S1=WENN($B1<=Q1;"";Q1+32-TAG(Q1+32))
T1=WENN(S1="";"";MIN(S1;$B1)-Q1)
U1=WENN($B1<=S1;"";S1+32-TAG(S1+32))
V1=WENN(U1="";"";MIN(U1;$B1)-S1)
W1=WENN($B1<=U1;"";U1+32-TAG(U1+32))
X1=WENN(W1="";"";MIN(W1;$B1)-U1)
Y1=WENN($B1<=W1;"";W1+32-TAG(W1+32))
Z1=WENN(Y1="";"";MIN(Y1;$B1)-W1)
B2=D2+F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2
C2=WENN(C1="";"";MONATSENDE(C1;0)-DATUM(JAHR(C1);MONAT(C1);TAG(1))+1)
D2=WENN(D1="";0;RUNDEN(4,3482/C2*D1*10;2))
E2=WENN(E1="";"";MONATSENDE(E1;0)-DATUM(JAHR(E1);MONAT(E1);TAG(1))+1)
F2=WENN(F1="";0;RUNDEN(4,3482/E2*F1*10;2))
G2=WENN(G1="";"";MONATSENDE(G1;0)-DATUM(JAHR(G1);MONAT(G1);TAG(1))+1)
H2=WENN(H1="";0;RUNDEN(4,3482/G2*H1*10;2))
I2=WENN(I1="";"";MONATSENDE(I1;0)-DATUM(JAHR(I1);MONAT(I1);TAG(1))+1)
J2=WENN(J1="";0;RUNDEN(4,3482/I2*J1*10;2))
K2=WENN(K1="";"";MONATSENDE(K1;0)-DATUM(JAHR(K1);MONAT(K1);TAG(1))+1)
L2=WENN(L1="";0;RUNDEN(4,3482/K2*L1*10;2))
M2=WENN(M1="";"";MONATSENDE(M1;0)-DATUM(JAHR(M1);MONAT(M1);TAG(1))+1)
N2=WENN(N1="";0;RUNDEN(4,3482/M2*N1*10;2))
O2=WENN(O1="";"";MONATSENDE(O1;0)-DATUM(JAHR(O1);MONAT(O1);TAG(1))+1)
P2=WENN(P1="";0;RUNDEN(4,3482/O2*P1*10;2))
Q2=WENN(Q1="";"";MONATSENDE(Q1;0)-DATUM(JAHR(Q1);MONAT(Q1);TAG(1))+1)
R2=WENN(R1="";0;RUNDEN(4,3482/Q2*R1*10;2))
S2=WENN(S1="";"";MONATSENDE(S1;0)-DATUM(JAHR(S1);MONAT(S1);TAG(1))+1)
T2=WENN(T1="";0;RUNDEN(4,3482/S2*T1*10;2))
U2=WENN(U1="";"";MONATSENDE(U1;0)-DATUM(JAHR(U1);MONAT(U1);TAG(1))+1)
V2=WENN(V1="";0;RUNDEN(4,3482/U2*V1*10;2))
W2=WENN(W1="";"";MONATSENDE(W1;0)-DATUM(JAHR(W1);MONAT(W1);TAG(1))+1)
X2=WENN(X1="";0;RUNDEN(4,3482/W2*X1*10;2))
Y2=WENN(Y1="";"";MONATSENDE(Y1;0)-DATUM(JAHR(Y1);MONAT(Y1);TAG(1))+1)
Z2=WENN(Z1="";0;RUNDEN(4,3482/Y2*Z1*10;2))


Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKLMNOPQRSTUVWXYZ
115.01.1503.12.15Jan 1517Feb 1528Mrz 1531Apr 1530Mai 1531Jun 1530Jul 1531Aug 1531Sep 1530Okt 1531Nov 1530Dez 153
2 462,853123,842843,483143,483043,483143,483043,483143,483143,483043,483143,483043,48314,21

ZelleFormel
C1=DATUM(JAHR(A1);MONAT(A1)+1;)
D1=MIN(C1;$B1)-A1+1
E1=WENN($B1<=C1;"";C1+32-TAG(C1+32))
F1=WENN(E1="";"";MIN(E1;$B1)-C1)
G1=WENN($B1<=E1;"";E1+32-TAG(E1+32))
H1=WENN(G1="";"";MIN(G1;$B1)-E1)
I1=WENN($B1<=G1;"";G1+32-TAG(G1+32))
J1=WENN(I1="";"";MIN(I1;$B1)-G1)
K1=WENN($B1<=I1;"";I1+32-TAG(I1+32))
L1=WENN(K1="";"";MIN(K1;$B1)-I1)
M1=WENN($B1<=K1;"";K1+32-TAG(K1+32))
N1=WENN(M1="";"";MIN(M1;$B1)-K1)
O1=WENN($B1<=M1;"";M1+32-TAG(M1+32))
P1=WENN(O1="";"";MIN(O1;$B1)-M1)
Q1=WENN($B1<=O1;"";O1+32-TAG(O1+32))
R1=WENN(Q1="";"";MIN(Q1;$B1)-O1)
S1=WENN($B1<=Q1;"";Q1+32-TAG(Q1+32))
T1=WENN(S1="";"";MIN(S1;$B1)-Q1)
U1=WENN($B1<=S1;"";S1+32-TAG(S1+32))
V1=WENN(U1="";"";MIN(U1;$B1)-S1)
W1=WENN($B1<=U1;"";U1+32-TAG(U1+32))
X1=WENN(W1="";"";MIN(W1;$B1)-U1)
Y1=WENN($B1<=W1;"";W1+32-TAG(W1+32))
Z1=WENN(Y1="";"";MIN(Y1;$B1)-W1)
B2=D2+F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2
C2=WENN(C1="";"";MONATSENDE(C1;0)-DATUM(JAHR(C1);MONAT(C1);TAG(1))+1)
D2=WENN(D1="";0;RUNDEN(4,3482/C2*D1*10;2))
E2=WENN(E1="";"";MONATSENDE(E1;0)-DATUM(JAHR(E1);MONAT(E1);TAG(1))+1)
F2=WENN(F1="";0;RUNDEN(4,3482/E2*F1*10;2))
G2=WENN(G1="";"";MONATSENDE(G1;0)-DATUM(JAHR(G1);MONAT(G1);TAG(1))+1)
H2=WENN(H1="";0;RUNDEN(4,3482/G2*H1*10;2))
I2=WENN(I1="";"";MONATSENDE(I1;0)-DATUM(JAHR(I1);MONAT(I1);TAG(1))+1)
J2=WENN(J1="";0;RUNDEN(4,3482/I2*J1*10;2))
K2=WENN(K1="";"";MONATSENDE(K1;0)-DATUM(JAHR(K1);MONAT(K1);TAG(1))+1)
L2=WENN(L1="";0;RUNDEN(4,3482/K2*L1*10;2))
M2=WENN(M1="";"";MONATSENDE(M1;0)-DATUM(JAHR(M1);MONAT(M1);TAG(1))+1)
N2=WENN(N1="";0;RUNDEN(4,3482/M2*N1*10;2))
O2=WENN(O1="";"";MONATSENDE(O1;0)-DATUM(JAHR(O1);MONAT(O1);TAG(1))+1)
P2=WENN(P1="";0;RUNDEN(4,3482/O2*P1*10;2))
Q2=WENN(Q1="";"";MONATSENDE(Q1;0)-DATUM(JAHR(Q1);MONAT(Q1);TAG(1))+1)
R2=WENN(R1="";0;RUNDEN(4,3482/Q2*R1*10;2))
S2=WENN(S1="";"";MONATSENDE(S1;0)-DATUM(JAHR(S1);MONAT(S1);TAG(1))+1)
T2=WENN(T1="";0;RUNDEN(4,3482/S2*T1*10;2))
U2=WENN(U1="";"";MONATSENDE(U1;0)-DATUM(JAHR(U1);MONAT(U1);TAG(1))+1)
V2=WENN(V1="";0;RUNDEN(4,3482/U2*V1*10;2))
W2=WENN(W1="";"";MONATSENDE(W1;0)-DATUM(JAHR(W1);MONAT(W1);TAG(1))+1)
X2=WENN(X1="";0;RUNDEN(4,3482/W2*X1*10;2))
Y2=WENN(Y1="";"";MONATSENDE(Y1;0)-DATUM(JAHR(Y1);MONAT(Y1);TAG(1))+1)
Z2=WENN(Z1="";0;RUNDEN(4,3482/Y2*Z1*10;2))

Vielleicht kannst Du ja was damit anfangen ...
to top
#10
Hi,

Zitat:aber zum berechnen sind es 17 Tage. ;-)

Arbeitstage ist wohl hierfür der falsche Ausdruck.


dann verstehe ich deine Berechnung nicht.


Zitat:Händische Formel:

Tage des vollen Monats 31 (da Januar)
Arbeitstage laut Vertrag 17
Wöchentliche Arbeitszeit 10 Stunden

Dann lautet unsere Formel nun so:
4,3482 / 31 * 17 *10= 23,84 = Die Stunden die er im Zeitraum 15.01. - 31.01 erbringen muss.

Und woher kommen die 4,3482?

Vielleicht solltest du uns deine Tabelle vorstellen und genau erklären, warum du wo etwas berechnest und wie das Ergebnis aussehen soll.

Hier kannst du nachlesen, wie das geht.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste