[EXCEL] Tijd berekenen

[eelcootjuh]

Mijn vraag

Ik heb een excel file welke iedere maand aangevuld wordt met data uit de voorgaande maand. Aan het eind van het jaar zijn er ongeveer 30.000 regels. We hebben hier een kolom Import Date en een kolom Contact Time. Ook heb ik de kolom Contacted in time, welke met een JA of NEE ingevuld moet worden.

Wanneer is het

code:

Contacted in time=Ja

code:

Contact Time - Import Date <= 1:00:00

.
Voorheen werd dit handmatig op JA of NEE gezet, want uit de reporting die we van externe krijgen is de Contact Time rood of groen gekleurd, en daarop werd een filter gezet. Maar om fouten te voorkomen lijkt het me ook mogelijk om dit met een formule te doen.

Wat ik nu heb is dit:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(Contact Time-Import Date<=1:00:00;"JA";"NEE"))

Deze formule werkt correct. Maar het probleem dat ik nu heb is dat ik geen rekening met secondes moet houden en dat gebeurd nu nog wel.

Voorbeeld

Import Date	Contact Time	Contacted in time
2/01/2016 8:04:35	2/01/2016 9:04:57	JA

Hier zouden we het antwoord JA moeten krijgen, want ik mag niet naar de secondes kijken, maar dat doet mijn formule wel, dus die geeft als resultaat NEE.

Import Date	Contact Time	Contacted in time
2/01/2016 8:04:35	2/01/2016 9:05:02	NEE

Hier krijg ik wel als antwoord NEE, en dat klopt ook want in minuten zit hij aan 61.

Nu ben ik zelf al langer opzoek geweest, maar omdat een datum/tijd niet meer is dan een getal, is het erg lastig om de seconden eruit te filteren/weg te halen.

Hebben jullie een idee wat ik zou kunnen gebruiken om het verschil met de secondes te negeren?

[Verwijderd]

Je kunt de tijdsfuncties hiervoor gebruiken. Dus in plaats van de datum(/tijd) direct te vergelijken deze te gebruiken in combinatie met UUR(), MINUUT() en DAG() etc.

Voorbeeld: =ALS(UUR(A1)>1;"Ja";"Nee")

Hierbij worden de minuten en seconden en de rest dus genegeerd. UUR() (of HOUR() ) haalt alleen het uur op uit de tijdsnotatie.

[ Voor 38% gewijzigd door Verwijderd op 31-03-2016 10:50 ]

[Jean Paul]

Het is als eerste belangrijk te begrijpen hoe de tijd in Excel geregistreerd wordt. Voor een dag geldt de waarde 1. Een dag heeft 24 uur, dus 1/24 staat gelijk aan 1 uur.

Wil je met minuten gaan rekenen, geldt dat elk uur 60 minuten heeft. Dus 1/(24*60) ofwel 1/1440 staat gelijk aan een minuut, en 30/1440 staat gelijk aan 30 minuten.

Voor seconden geldt dus 1/(24*60*60) ofwel 1/(1440*60) ofwel 1/86400, en 30 seconden is ook hier weer 30/86400

Overigens snap ik de aanname niet dat een blanco waarde gelijk staat als not in time. Dit kan je relateren aan de huidige tijd: NOW(). Deze update niet real time, maar bij openen, bewerken, opslaan e.d. wel weer.

Wat jij zoekt is:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(Contact Time-Import Date<=(1/24);"JA";"NEE"))

Edit:
Sorry, niet alles gelezen

Om de seconde te negeren kan je de datum en tijd loshalen en tegelijkertijd de seconde hard op 0 zetten. Dit doe je grof gezegd met: (INT(datum)+TIME(HOUR(datum);MINUTE(datum);seconde=0))

De INT() zorgt ervoor dat alles achter de comma (dus tijdstempel) wegvalt. Daarna gebruik je de TIME() functie om het uur en de minuut uit de datum te trekken, en zet je de seconde op 0 (want irrelevant).

Bij een aftreksom van tijd wordt er het e.e.a. afgerond in Excel achter de schermen. Normaliter krijg je bij een uur een waarde van 0,04166666666666666666666666 (etc.) maar bij de aftreksom kom je uit op 0,0416666666715173. Hierdoor moet je met afrondingen werken tot de 11^ste decimaal. Dit ziet er als volgt uit:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(ROUND((INT(Contact Time)+TIME(HOUR(Contact Time);MINUTE(Contact Time);0))-(INT(Import Date)+TIME(HOUR(Import Date);MINUTE(Import Date);0));11)<=ROUND(1/24;11);"JA";"NEE"))

Aangezien Excel niet helemaal zuiver is met dit soort berekeningen, kan je het beste alleen met waarden werken, dus niet met functies zoals TIME() en DATE() etc.

Je wilt een uur weten op basis van de kleinste noemer, zijnde de minuut. De datumvelden gaan we daarom als volgt omrekenen naar minuten: INT(Contact Time*1440) en INT(Import Date*1440).
Vermenigvuldigen met 1440 aangezien één dag 1440 minuten bevat. De INT() functie kapt alles achter de comma weg, wat seconden zijn.

Nu kan je veel overzichtelijker en precies rekenen:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(INT(Contact Time*1440)-INT(Import Date*1440)<=60;"JA";"NEE"))

En aangezien we op basis van minuten rekenen, moet je de vergelijking ook doen in minuten, dus kleiner dan of gelijk aan 60

[ Voor 77% gewijzigd door Jean Paul op 31-03-2016 11:57 ]

[Eppo ©]

Is dit niet het meeste makkelijke:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(INT((Contact Time-Import Date)*1440)<=60;"JA";"NEE"))

Je doet de twee tijdstippen van elkaar afhalen, dan vermenigvuldig je dit met 1440 om minuten te krijgen. Dit rond je af naar beneden om de seconden kwijt te raken. Daarna kijk je of de waarde gelijk of minder is dan 60.

Edit: Laatste formule van Jean Paul niet gezien, maar die komt op hetzelfde neer en is net zo makkelijk. Had alleen die één na laatste formule gezien en dan was mijn formule simpeler.

[ Voor 21% gewijzigd door Eppo © op 04-04-2016 11:14 ]

[F_J_K]

Het was niet de bedoeling om de seconden mee te laten tellen. Afhankelijk van hoe streng je mag/moet zijn moet je dat wat corrigeren.

11:59:59 --> 13:00:01 ~~~> zou NEE zijn (13:00 - 11:59 = 61min).
12:00:00 --> 13:00:59 zou JA zijn (13:00-12:00 = 60min) maar hier is langzamer teruggebeld.

Afhankelijk van de strengheid kan je misschien de lat leggen bij 61 minuten. alsnog de seconden meewegen en daarna pas naar beneden afronden. Maar hoe om te gaan met afronden moet zijn af te leiden uit de gemaakte afspraken en/of het formulier waar de kleurcode wordt getoond.

[Jean Paul]

Eppo © schreef op maandag 04 april 2016 @ 11:08:
Is dit niet het meeste makkelijke:

code:

=IF(ISBLANK(Contact Time);"NEE";IF(INT((Contact Time-Import Date)*1440)<=60;"JA";"NEE"))

Je doet de twee tijdstippen van elkaar afhalen, dan vermenigvuldig je dit met 1440 om minuten te krijgen. Dit rond je af naar beneden om de seconden kwijt te raken. Daarna kijk je of de waarde gelijk of minder is dan 60.

Edit: Laatste formule van Jean Paul niet gezien, maar die komt op hetzelfde neer en is net zo makkelijk. Had alleen die één na laatste formule gezien en dan was mijn formule simpeler.

Let wel dat bij jouw formule de seconden nog meetellen, en dat wilde de TS niet

@TS: Hoe staat het ermee? je vraagt hulp, krijgt wat reacties.. en verder geen reactie van jou meer?

[ Voor 6% gewijzigd door Jean Paul op 06-04-2016 08:27 ]

[Eppo ©]

Ik zie inderdaad mijn fout. Mijn formule rekent eerst het verschil uit en gaat daarna pas afronden terwijl de jouwe eerst de tijd afrond naar hele minuten en daarna pas het verschil uitrekent.

In dat geval heb ik dus niks gezegd en klopt jouw formule helemaal.