Fformiwla Excel: Cyfrifwch ddiwrnodau sy'n gorgyffwrdd rhwng dwy ystod dyddiad
Awdur: HaulWedi'i Addasu Diwethaf: 2020-07-13
Yn y tiwtorial hwn, mae'n darparu fformiwla i gyfrifo'r diwrnodau sy'n gorgyffwrdd rhwng dwy ystod dyddiad yn Excel. Er enghraifft, yr ystod dyddiad a roddir gyntaf yw 1/1/2020-6/20/2020, ac aiff prosiect A ymlaen yn yr ystod dyddiad 8/16/2019-1/2/2020, mae 2 ddiwrnod yn gorgyffwrdd rhwng y ddau ystod dyddiad hyn fel y dangosir y screenshot uchod.
Os ydych chi am ddilyn ynghyd â'r tiwtorial hwn, lawrlwythwch y daenlen enghreifftiol.
Fformiwla generig:
MAX(MIN(end1,end2)-MAX(start1,start2)+1,0)
Dadleuon
End1, start1: the end date and start date in the first date range.
End 2, start2: the end date and start date in the second date range.
Gwerth dychwelyd
Mae'r fformiwla yn dychwelyd y diwrnodau sy'n gorgyffwrdd â gwerth rhifol.
Sut mae'r fformiwla hon yn gweithio
Yma, mae'r prosiectau'n cael eu symud ymlaen mewn gwahanol ystodau dyddiad, rydym am gyfrifo'r diwrnodau sy'n gorgyffwrdd rhwng ystod dyddiad pob prosiect ac ystod dyddiad penodol yn Ystod C2: C3.
Yng nghell H3, defnyddiwch y fformiwla isod:
=MAX(MIN($C$3,G3)-MAX($C$2,F3)+1,0)
Pwyswch Rhowch allwedd yna llusgwch handlen autofill isod i gyfrifo'r dyddiau sy'n gorgyffwrdd.
Esboniad
Defnyddir swyddogaeth MIN i ddod o hyd i'r gwerth lleiaf mewn llawer o werthoedd. Yma mae'n darganfod y dyddiad cynharach rhwng y ddau ddyddiad gorffen.
Defnyddir swyddogaeth MAX i ddod o hyd i'r gwerth mwyaf mewn gwerthoedd.
Mae'r swyddogaeth MAX fewnol yn darganfod y dyddiad diweddarach rhwng dau ddyddiad cychwyn.
I gynnwys dyddiad cychwyn a dyddiad gorffen, gwnaethom wrthbwyso'r canlyniad trwy ychwanegu 1.
=MAX(MIN($C$3,G3)-MAX($C$2,F3)+1,0) = MAX (43832-43831 + 1,0)
Defnyddir y swyddogaeth MAX allanol i arddangos y gwerth negyddol yn lle sero.
Fformiwlâu Perthynas
Cyfrifwch Dâl Goramser Yn y tiwtorial hwn, mae'n dangos taflen amser i restru'r oriau gwaith, ac mae'n darparu fformiwla i gyfrifo'r tâl goramser.
This comment was minimized by the moderator on the site
Das ist super und der halbe Weg zu der Lösung meines Ziels: Start Date und End Date markieren bei mir einen geplanten Projekteinsatz, die Liste der abzugleichenden Daten sind Abwesenheiten in einer gesonderten Tabelle. Ich möchte neben der geplanten Projekteinsatzzeitspanne die Summe der überlappenden Tage, die sich aus den Abwesenheiten ergeben, also in dem Beispiel hier 133 sehen. Eine Idee wie das geht? Ich habe schon eine Formel, die mir alle Start- und Endzeiten der Überlappenden Abwesenheiten anzeigt, dabei habe ich die Filterfunktion benutzt, aber bei den konkreten Summen komme ich bisher nicht an...
Keine Ahnung ob das jetzt mit dem Foto klappt, ich probiere mal 2 Varianten.
