Preskoči na glavno vsebino

Kako izračunati odstotek med dvema datumoma v Excelu?

Recimo, da imam seznam nalog z začetnim datumom in končnim datumom, zdaj želim izračunati odstotek dokončanja opravila glede na današnji datum, kot je prikazano na spodnji sliki zaslona. Kako ste lahko to nalogo rešili v Excelu?

Izračunajte odstotek dokončanja med dvema datumoma s formulo

Izračunajte odstotek dokončanja med dvema datumoma s formulo

Za izračun odstotka dokončanja naloge med dvema datumoma uporabite spodnjo formulo:

1. Vnesite to formulo v prazno celico, kjer želite dobiti rezultat:

=(DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)

Opombe: V tej formuli, B2 je celica z datumom začetka in C2 je celica končnega datuma.

2. Nato povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo, in dobili boste rezultat, kot je prikazano na spodnji sliki zaslona:

3. Nadaljujte z izbiro celic formule in kliknite Odstotni slog pod Domov Jeziček na Število skupini, in dobili boste odstotno vrednost, kot je prikazano na sliki:

Več relativnega odstotka člankov:

  • Izračunajte prvotno ceno glede na odstotek popusta
  • Običajno v našem vsakdanjem življenju obstajajo nekateri izdelki, ki so v nakupovalnem središču znižani. Če imate prikazano ceno in odstotek popusta, kot je prikazano na sliki spodaj, kako lahko v Excelu izračunate prvotno ceno tega izdelka?
  • Izračunajte odstotek preteklega leta ali meseca
  • Recimo, da imate na delovnem listu seznam datuma, zdaj bi radi dobili odstotek leta ali meseca, ki je pretekel ali je ostal na podlagi določenega datuma. Kako ste lahko rešili to nalogo v Excelu?
  • Izračunajte odstotek da in ne s seznama
  • Kako lahko izračunate odstotek besedila z da in brez besedila s seznama celic obsega na Excelovem delovnem listu? Mogoče vam bo ta članek pomagal pri reševanju naloge.
  • Ustvarite grafikon tako z odstotki kot z vrednostjo
  • Za stolpec ali stolpčni grafikon lahko enostavno dodamo odstotek ali vrednost, toda, ali ste že poskusili ustvariti stolpec ali stolpčni grafikon, tako da sta v Excelu prikazana tako odstotek kot vrednost?
  • Števec za izračun odstotka v Excelu
  • Na primer, imam zbirno poročilo raziskovalne naloge in obstajajo tri možnosti A, B, C, zdaj želim izračunati odstotek vsake od teh treh možnosti. Se pravi, da moram vedeti, da možnost A predstavlja odstotek vseh možnosti. V tem članku bom govoril o tem, kako pridobiti odstotek določene možnosti v obsegu.
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Comments (24)
No ratings yet. Be the first to rate!
creativekat
creativekat
This comment was minimized by the moderator on the site
Hello - Is there a way to combine both these options?

So that the calculation will show O instead of #NUM! if the date is in the future AND stay at 100% if the end date has passed.

That would be the ideal thing.
Reply
Report
0
0
creativekat
creativekat
This comment was minimized by the moderator on the site
Is there a way to incorporate both these items? I would like it to stay at 100% once the date passes and that it shows 0% instead of the #NUM! error if the date is in the future?
Reply
Report
0
0
Alan
Alan     creativekat
This comment was minimized by the moderator on the site
I am using the formula:

=MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))

which is working great and leaves the percentage at 100% once the end date has passed.
Reply
0
0
Sebatian
Sebatian
This comment was minimized by the moderator on the site
How can you calculate a date difference in days using a percentage in excel?

Basically, I'm looking to implement a start date for an activity based on the fraction of 15/52. The idea is that an activity starts 3 weeks from a kick-off date.

However, a series of projects have variable durations. As such I want to represent 15 weeks out of 52 weeks in a formula I can use across all projects to work on the same principle.

The idea is to use a start date and project into the future using a percentage to define the next start date for another activity to commence.
Reply
0
0
Murilo
Murilo
This comment was minimized by the moderator on the site
Qual a formula para não dar mais que cem e menos de zero?
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
И как быть, если дата начала не определена, а дата окончания известна. В этом случае по формуле пишет 100%. А хочется чтобы писал 0 или ничего.
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
Привет! Спасибо за формулы!

Что добавить в формулу, чтобы не ругался, если дата начала и окончания совпадают? (предполагается, что задача выполняется в теч. дня.
Reply
0
0
Натали
Натали
This comment was minimized by the moderator on the site
Привет! Спасибо за формулы!! Весь интернет перерыла, нашла только у вас.
Ругается "параметр 2 не может равняться нулю", когда дата начала и дата окончания одна и та же (заложено, что задача делается в течении дня). Что добавить в формулу?
Reply
0
0
adit
adit
This comment was minimized by the moderator on the site
kenapa jika tepat waktu hasil nya menjadi tidak 100%?
Reply
0
0
MandyZhou
MandyZhou     adit
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)).
It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Ona
Ona     MandyZhou
This comment was minimized by the moderator on the site
Thank you for this, help me a lot. But how to show blank if one of the date is not input yet? Because it keep shows 100%.
Reply
0
0
indraputra
indraputra     adit
This comment was minimized by the moderator on the site
waktunya habis jam 12 tgh malam..jadi 12.01 baru akan bertukar kepada 100%. harap membantu
Reply
Report
0
0
MandyZhou
MandyZhou     indraputra
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). 
It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Carlo
Carlo     MandyZhou
This comment was minimized by the moderator on the site
Hi Mandy,
I am trying to exeute =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)) but it gives me always an error.
I am using a MAC, but I don't think is matter of OS.
THANKS
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
thank you for the formula, when i use it for a dates in the future it returns #NUM!. is there a way to get it to return a 0.00 or some sort of text return like "Not Started"? it would be cleaner and more informative and i have other formulas based on the response so having the option to return a 0.00 (and or text options) would be greatly appreciated. thanks!
Reply
0
0
skyyang
skyyang     Jeff
This comment was minimized by the moderator on the site
Hi, Jeff,To solve your problem, please apply the below formula:=IFERROR((DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1),"Not start").
Please try, hope it can help you!

Reply
Report
1
0
indraputra
indraputra     skyyang
This comment was minimized by the moderator on the site
Hi skyyang, very helpful this one. How about if today date is exceed the end date, how to maintain it at 100% instead of 100 over percent.
Reply
Report
0
0
MandyZhou
MandyZhou     indraputra
This comment was minimized by the moderator on the site
Hello,Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.Sincerely,Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Indra
Indra     skyyang
This comment was minimized by the moderator on the site
Hi skyyang, very helpfull this one. How about if today date is exceed the end date, how to maintain it at 100% instead of 100 over percent.
Reply
0
0
MandyZhou
MandyZhou     Indra
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)). It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
Adib
Adib     Jeff
This comment was minimized by the moderator on the site
same here.
Reply
0
0
MandyZhou
MandyZhou     Adib
This comment was minimized by the moderator on the site
Hello,
Gald to help. Please use this formula: =MIN(1, (DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1)).

It will maintain it at 100% instead of 100 over percent if today date is exceed the end date. Please see the screenshots. In cell D2, I use the formula in the article, and the result is 156.25%. In cell E2, I use the new formula I offered in this comment, and the result is 100%. Hope it can solve your problem. Have a nice day.
Sincerely,
Mandy
View Attachments (2 / 5)
Reply
Report
0
0
bertina
bertina
This comment was minimized by the moderator on the site
how do i find a 62% percentage date between two dates
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations