Újabb idegesítő Excel-funkció, újabb megoldás és magyarázat

Bizony, időnként negatív időmegjelenítésre is szükségünk lehet!

2015. október 19. - Office Guru

Tovább folytatva idegesítő alapbeállítások és funkciók elleni harcunkat Excelben, most vegyük górcső alá a negatív idő megjelenítésének problémáját, amely első látásra-hallásra nevetségesnek tűnik, de ha mondjuk egy munkaidő-nyilvántartó fájlt szeretnénk készíteni, akkor lehet jelentősége annak, hogy az előírtnál kevesebbet dolgoztunk. Persze máshogy is meg lehet ezt a problémát oldani, de ahogy a képen is látjuk, alapesetben ##### jut nekünk osztályrészül, ha simán kivonjuk egymásból időinket:

280.jpgEgy rendkívül gyors, ám óvatosan kezelendő megoldás az Excel Options Advanced füle alatt elérhető "Use 1904 date system" bekapcsolása, amely után egyértelműen látható az, amit szerettünk volna elérni:

281.jpg

282.jpgEzzel azonban igencsak csínján kell bánnunk, hiszen eléggé gyorsan tönkrevághatjuk más, szintén dátumokra épülő képleteinket is ugyanabban a workbookban, szóval inkább maradjunk egy függvényes megoldásnál, amely a TEXT formula képében siet segítségünkre.

Excelben a szövegformátum elég sok olyan érték megjelenítését is lehetővé teszi, amelyeket mondjuk számként nem tudnánk elérni, például így van esélyünk a 0001 értéket beadnunk egy cellába anélkül, hogy az mondjuk automatikusan 1-re változzon és a szövegformátum azt is lehetővé teszi, hogy negatív időt jelenítsünk meg.

A TEXT függvény pedig pontosan ezt teszi, fogja az első paramétereként megadott értéket és a második paraméterként megadott formában szövegként adja vissza nekünk.

Jelen esetben ahogy a képen is látható, fogjuk két időpontunk különbségét, majd összehasonlítjuk a kötelező munkaidővel és az egész eredményt szöveggé alakítjuk:

=TEXT((F6-E6)-J3,"H:MM")

283.jpgÉs ezzel kész is vagyunk, úgy jeleníthetjük meg az időnket, ahogy akarjuk, persze azért mindig koncentráljunk arra, hogy ledolgozott munkaidőnk sose menjen negatívba...

De álljunk csak meg a poszt végén még egy szóra, hiszen anno engem is majd megőrjített a kérdés, hogy mi szükség volt két időrendszerre az Excelben, tehát mi különbség van az 1904-es és 1900-as idők között? Linkelhetném a Microsoft hivatalos oldalát is, de alapvetően elég egyszerű a magyarázat: az 1900-as időszámítási rendszert anno azért fejlesztették ki, hogy kompatibilis legyen a Lotus 1-2-3 által készített táblázatokkal (amelyek ezt a számítási módot használták dátumaikhoz), míg az 1904-es rendszert azért, hogy a Macintosh felhasználói is használhassák az Excelt, hiszen a korai Mac gépek nem támogatták az 1904. január elseje előtti dátumok használatát. Szóval attól függően, hogy éppen Macről vagy Windowsról származó Excel fájllal szembesülünk, mindenképpen figyelembe kell vennünk ezeket a dátumbeállításokat, hogy későbbi problémákat el tudjunk kerülni.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr97991431

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

GABOR2 2015.10.20. 19:57:14

Nemcsak az excel-lel hanem magával a Windows 10-zel is sok baj van. Írtam róla a blogomban. www.ujtechkor.blog.hu/

TZoli 2015.10.22. 10:21:42

Helló!

Először is gratulálok a bloghoz. Érdekes témák, érdekes megoldások. Nemrég találtam ide, nagyon tetszik. :)

Az aktuális téma leírt megoldása nálam nem működik Excel 2010-ben, ha "ó:pp" formátumot alkalmazom a SZÖVEG függvényben. De ha csak "pp", azaz csak percben történik a kijelzés, akkor az igen. Verziófüggő lenne a megoldás?

Másrészt a pl. 0001 bevitele és kijelzése megoldható sima gépeléssel, csak aposzrófjellel kell kezdeni. Ez sok más esetben is működik, és nem kell a Számformák között a Szöveg beállítást alkalmazni.
Abban az esetben, ha számított értékekről van szó vagy mégis formáznánk, akkor az Egyéni számformátumoknál kódként elég pl. 0000 kódsort megadni és minden 1000-nél kisebb szám 4 számjegyűként vezetőnullákkal kerül kijelzésre. Ráadásul akit zavarnak a kis zöld jel a cellákban, utóbbi megoldás "lehagyja".

penumbra1984 2016.01.20. 08:58:41

Én a 1904-es dátumrendszert kapcsoltam be, elegánsabb, nem kell TEXT függvénnyel bajlódni, ezenfelül ha szövegként kapod vissza a negatív számot, akkor hogyan számolsz vele tovább? Akkor a textet megint vissza számmá...
Ez a negatív idő nemkijelzése is csak a szokásos érthetlen "csakmert" megoldás az Excelben.
Valamiért Office alkalmazások tele vannak ilyen érthetetlenül buta, körülményesen áthidalható marhasággal, Word is képes az őrületbe kergetni az embert. Próbáltad már például kitörölni a végjegyzetek listája előtti kis vízszintes vonalat? Nem írom ide a megoldást, házi feladat megkeresni, abszurd. De utána is benne marad egy bekezdésjel és azt sehogysem fogod törölni. Vagy a végjegyzetek listáját próbáld úgy átmásolni egy külön dokumentumba, hogy a számozás megmaradjon. Nem fog menni, mindenhol visszaugrik 1-re. Miért? Csak!
Word űrlapok: ha olyan az űrlapod, hogy a kitöltő nem tölt ki minden mezőt mert mondjuk nem szükséges, akkor nyomtatásban is látszani fog a mezőben a halványszürke helyőrző szöveg, hogy "Írjon ide valamit" vagy valami hasonló. Keresed a kis jelölőnégyzetet mindenhol, hogy hátha van olyan, hogy "helykitöltő szöveg nyomtatásban látszik/nem látszik". Nincs ilyen. Miért? Csak!
Kettő megoldás van. Vagy nekiállsz valami VB scriptet/makrót hegeszteni, ami figyeli a mező tartalmát és valamit birizgál a kódban, hadd ne mondjam ez mekkora szenvedés. Vagy egyszerűen egy szóközt írsz az üresen hagyni kívánt mezőben, de ez meg mennyire csehszlovák módszer már?
Továbbá Excelben az egyéni számformátumok megadásánál nem található semmi referencia, hogy milyen szintaktika alkalmazható. Annyi, hogy módosíts egy meglévőt. De hogyan? És ha én egy olyat akarok létrehozni ami teljesen új? A súgó mélyen hallgat erről.
Mert van különbség az óó:pp formátumkód és a [óó]:pp formátumkód között!
Szeretjük az Office-t, de időnként megkínoznánk a programozóit. :)