# Microsoft Office Application Help - Excel Help forum > Non English Excel >  >  Data and time reminder

## Lozus

Hello.
Now I am using this code (att.), but some time it work correctly, sometimes not.
It would be great if in one sell I can write date and time (like reminder) and this cell will check current date and time (att.)

Thank you.

----------


## bmouse

Sveiks!

Atvainojos, bet nesapratu tavu problēmas izklāstu angliski.
Uzraksti lūdzu latviski vai krieviski (nezinu, kādas tautības esi), kāds tieši ir vēlamais rezultāts, varbūt izdosies tev palīdzēt  :Smilie:

----------


## Lozus

Spasibo.
Estj baza klientov kotorim inogda nado otzvonitj. Naprotiv klienta ja pishu datu i vremja, potom vruchnuju idu cherez spisok ili stavlju filjtr i smotrju komu segodnja i vo skoljko nado pozvonitj. No spisok stanovitjsa uzhe takoj boljshoj i tak mnogo dat chto tjazhelo tak rabotatj.
Poetomu hotelosj chtobi datu i vremja excel sravnival s tekuschim vremenem. I esli tekuschee vremja bolshe, to (naprimer v A1) ja vizhu kakuju-to informaciju. Naprimer nado pozvitj klientu 1. ili "alert in cell b22".

Nadejusj ponjatno objasnil  :Smilie:

----------


## bmouse

Ja sovetuju izpolzovatj *Conditional Formatting* i postavitj kakoj nibutj cvet tem klientam, kotorym nado otzvonitj. *Conditional Formatting* nahoditsa pod tabom *Home*. Znachit vydelajem odnu jacheiku, berjom *Conditional Formatting*, potom *Manage Rules*, potom *New Rule* i stavim poslednij vybor *Use a formula...*

Stavim takuju formulu =IF($E$20>D22;TRUE;FALSE)

Formula sravnivaet datu i vremja s sevodneshnej datoj i vremenem.

Nazhymaem na *Format* i stavim kakoj nibutj cvet jacheiki ili bukv. Potom *OK*.

Shtoby postavitj na vsju tablicu, vydelajem jacheiku gde postavili Conditional Formatting, berjom Format Painter tozhe pod tabom Home i vydelajem vsju tablicu. Takim obrazom Conditional Formatting avtomatom postavit formulu vesde.

Nadejus ponjatno, posmotri primer, esli shto napishi.

Udachi  :Smilie:

----------


## Lozus

Takoj variant ja ispoljzuju na dati, no hochu takzhe i na vremja. A vot na vremja ne mogu zastavitj korektno rabotatj. True ili false naprotiv kazhdoj jachejki ne variant. Nado chtobi odna jachejka "sledila" za vsem stolbcom.
Naprimer v stolbce B dati i vrema. Esli hotj odno data i vremja propuscheno, to v A1 menjaetsja cvet. Ne naprotiv kazhdoj, a v odnoj.
Funkcija =now() ne vsegda obnavljaet vremja.

----------


## bmouse

Stolbik s TRUE i FALSE ja prosto tak zapisal shtoby formulu proveritj, eto mozhno stiratj. Formula rabotaet na datu i na vremja tozhe. Esli budet menshe dazhe na paru sekund ot formuli NOW() ne budet vydelen krasnym.

Ladno, ja damoj, rabota zakonchilasj, podumaju nad tvojej prosboj, i zavtra posmotrju, shto mozhno sdelatj.

----------


## Lozus

Spasibo.
Tak vsjo podhodit, no vremja ne obnovljaet.

----------


## bmouse

Ne ponjal. Shto imenno ne obnovlajetsa? Ja pomenjal format dati, ot 21/01/2015 na 21.01.2015, takim obrazom excel ponimaet shto v jacheiki zapisana data i vremja, i berjot eto v uchot esli sravnivat s NOW()

----------


## bmouse

Ok, 2 varianta sdelal.

1) Mozhno postavitj kolonnu s formuloj IF i sravnivatj s formuloj NOW. Formula IF takaje zhe kak izpolzovali v CF.

2) Ja napisal malenkij makros kotoryj proverajet vesj stolbik klientov, potom sravnivaet s formuloj NOW. Kogda nahodit pervovo klienta kotoryj prosrochen, pokazyvajet v M19, stavit krasnyj cvet i ostanavlivaetsa. Poka klienta ne stiretj ili kakim to drugim obrazom neotmetitj shto vsjo v porjadke, makros dalshe rabotatj ne budet. Posle tovo nado makros zapuskatj opjatj, i on budet iskatj dalshe. Esli v spiske na dannij moment nebudet ne odnovo klienta kotoryj prosrochen, makros v M19 postavit OK i zeljonyj cvet.

Problema v tom shto nado perepisatj nomera jacheek v makrose shtoby tot rabotal v tvoj tablice. Poka on sdelan tak shtoby rabotal tolko v kolonne I i J.

Poprobuj pomenjatj vremja v kolonne J na prosrochennoe i nazhmi RUN, potom pomenjai opjatj, shtoby nebylo prosrochenno i opjatj RUN, posmotrish kak rabotaet.

Aa, i formula NOW obnovlaetsa kazhdyj raz kogda kliknutj 2 raza gde nibutj, ili makros zapuskatj, i tak dalee.

Udachnovo dnja  :Smilie:

----------


## Lozus

V pricipe super i boljshoe sposibo.

No kak vsegda hochetsja boljshe  :Smilie: 
1) Neljzja chtobi macros rabotal bez nazhatija RUN?
2) Sejchas on nahodit blizhajshuju datu po spisku. Neljzja chobi bilo po vremeni? T.e. naibolee prosrochenij chtobi pojavljalsja snachala?

----------


## bmouse

1) Mozhno, no skolko ja smotrel, kod kakoj to slozhnovatyj, nesmog razobratsa shto i kak, po skolku ja v principe ochenj bolshoj chainik po makrosam, 2 mesjaca nazad vobsche neznal shto eto takoe  :Smilie: 

Mozhno probovatj cherez google iskatj HOW TO RUN MACRO WITHOUT A BUTTON, esli sam ponimaesh shto to v makrosah, mozhesh probovatj dobavitj.

2) Ja hotel sdelatj immeno tak, no opyta nehvataet. Mne prishlosj delatj tak shtoby makros posle pervoj nahodki ostanovilse, inache vsegda budet pokazyvatj OK, esli poslednaja zapisj stolbika v norme. Nado bylo by stavitj shtoby on sravnival vse nahodki mezhdu saboj, no ja ne predstavlaju, kak eto delatj.

A ne mozhno sdelatj sortirovku spiska shtoby samaja malenkaja data/vremja byla sverhu?

----------


## Lozus

Jasno. I tak ochenj horosho.
Neljzja. Klienti sortirujustsja po drugomu principu.
Mozhete posmotretj att. chto ja delaju ne tak?

----------


## bmouse

Probaesh pustitj makros? Fail nado sohranatj kak Macro Enabled Workbook .xlsm shtoby ja smog posmotretj, prostoj .xls sohranaetsa bez makrosa.

Makrosy nahoditsa pod Developer Tab, View Code. Nado vzjatj moj fail, otkrytj View Code, makros nahoditsa pod Sheet1, potom copy/paste v svoj fail, tozhe pod Sheet 1 ili tam gde nahoditsa dannye. Nu i koneshno nado adresa jacheek pomenjatj, shtoby savpalo s adresamy v tvojom faile.

Shtoby knopku postavitj, tozhe pod Developer Tab, Insert, Form Controls, Button.

Nu esli shto, vot vesj kod:




```
Please Login or Register  to view this content.
```

----------


## Lozus

Ja eto vsjo vrode ponimaju. Jacheiki pomenjal (da i proboval stavit v tezhe nomera jacheek chto i u tebja), macros rabotaet, vremja obnavljaet no vsjo vremja govorit chto estj prosrochenie dati.

----------


## bmouse

Ok, shto to s knopkoi ne tak bylo. Knopku stavish tozhe iz Developer Tab i potom nado Assign Macro.

Ja pomenjal knopku, probuj teperj.

----------


## Lozus

Ja knopku stavil iz Activex Control (ne znaju pozhemu). Teperj vsjo ponjatno.

SPASIBO.

----------


## bmouse

Slushaj, kazhetso poluchilasj postavitj proverku vremeni.

Teperj vydajot klienta, kotoryj naibolee prosrochen, posmotri pozhalosta.

----------


## Lozus

Da voobsche super!!!!

Toljko ja choto tuplju. Sejchas u tebja informacija berjeotsja iz I. V svoej tablice v kode ja I menjau na A i u menja pihetsja ALERT i boljshe nichego.

----------


## bmouse

Ja sdelal tak shtoby iz kolonny J data i vremja stavitsa v pamjatj esli menshe formuly NOW (eto v makrose ANSWER), potom nazvanie klienta iz kolonny I tozhe stavitsa v pamjatj (eto v makrose KLIENTS). Esli nahoditsa v stolbike vremja menshe toi, kotoraja uzhe v pamatji postavlenna, ona zamenajetsa. Kogda vesj stolbik proveren, vydajotsa nazvanie poslednevo klienta iz pamjatji. Iz za proverki vremeni v samom stolbike nado bylo strukturu makrosa menjatj, poetomu teperj nemnozhko po drugomu vsjo.

Ja tozhe muchilse s tem shto vydajot tolko ALERT, no kogda nemnozhko pomenjam mesto nahazhdenija formul v makrose vsjo srabotalo.

V principe dolzhno bytj tak: kolonna I eto nazvanie klienta, kolonna J eto vremja, kolonna L eto formula NOW, kolonna M eto rezultat makrosa. Tam ostalsja drugoj makros pod nazvaniem PROVERKA. On ne nuzhen mozhno stiratj.

Nadejus srabotaet i u tebja.

----------


## Lozus

On uporno berjot dannie iz tvoego makrosa i ja ne mogu pemenjatj. Mozhesh sdelatj:

Client C
Tekuschee vremja I1
Data kogda zvonitj M
Informacija Pojavlnajetsja N1

Kogda pojavljatsja okno s klientom komu zvonitj neljzja chobi tekst mozhno bilo skopirovatj? Ili chobi kak link bil?


No uzhe spasibo. Na takoe ja dazhe i ne nadejalsja  :Smilie:

----------


## bmouse

Nadejus ja pravilno ponjal. Perestavil, kazhetsa u menja rabotajet vsjo.

Ja ubral frazu ALERT, shtoby pokazyval tolko nazvanie klienta i postavil shtoby vysvetilse rjad gde nahoditsa klient.

----------


## Fotis1991

*bmouse*

Op posted the question in Excel General sub forum and not to the NON English Sub Forum.

That means that (s)he can read and write English. 

A welcome from you to him in your lanquage could be enough. When you replied in your lanquage and then Op did that, the result is that we have 21 posts here that no one except of both of you understand what happens.

So pls if the thread *is not* in the Non English Sub Forum(in which i'll remoove this thread now), use the English lanquage for your replies.

Thank you.

----------


## bmouse

> *bmouse*
> 
> Op posted the question in Excel General sub forum and not to the NON English Sub Forum.
> 
> That means that (s)he can read and write English. 
> 
> A welcome from you to him in your lanquage could be enough. When you replied in your lanquage and then Op did that, the result is that we have 21 posts here that no one except of both of you understand what happens.
> 
> So pls if the thread *is not* in the Non English Sub Forum(in which i'll remoove this thread now), use the English lanquage for your replies.
> ...



Noted, apologies.

----------


## Lozus

Yes. In your file is excellent.
But if I paste code to my file, N1 always is empty and red. If I press button it fleshing to green OK.
In att part of my table

----------


## bmouse

Yes, column M has to contain correct date and time stamps, because a blank cell is treated as 01.01.1900 00:00. Also no extra simbols are allowed besides correct date and time, because it messes up the formatting. If there is an empty cell in column M, the macro will always pick that value as the lowest.


V kolonne M dolzhna bytj data i vremja v pravilnom formate bez dopolnitelnih simbolo. Takzhe bez pustyh jacheek, potomu shto pustaja jacheika s tochki zrenija excel soderzhyvaet datu i vremja 01.01.1900 00:00. Esli budet pustaja jacheika, makros vsegda budet pokazivatj N1 kak krasnuju.
Mozhet sdelaj fail s kuskom tvojei tablici, ja posmotrju pochemu do seh por nerabotaet.

----------


## Lozus

You can write only in english.
Its clear now.
If even I dont need any data in cell I must type and hide with colour or something like this.
But can I specify to check from M6?

----------


## bmouse

If you want to start the search from M6, edit the macro where it says *StartNumber=1* to *StartNumber=6*

----------


## Lozus

Really great work for me. Now everything is exactly what need.

Thank you!

----------


## bmouse

Ok, I think I made some mistakes in the code.

Try now, please.

----------


## Lozus

Ok. But that code also is good. But I will use this.
One more idea  :Smilie: 
Now I use M6:M99999. Because I dont want to change last M cell every day. Is it possible what code search in M till last cell what is not empty?

----------


## bmouse

At this point I don't know what code to use for that, sorry. Yes, you can expand the range of M, also you have to change the value of EndNumber, but EndNumber cannot be bigger than the number of last row that contains data. If EndNumber is bigger then the macro will show red and empty cells in column M.

----------


## Lozus

Understood.
Now I put till M9999 01.01.3000 and made it white. When I need I just change data to real.

----------


## Lozus

Hello.
Is it possible to make "find next/soonest appointment", not when time already is over?

----------


## bmouse

I'm sure its possible, but at this point its too complicated for me.

----------

