excel tarih aralığındaki aya ait günleri bularak tutarların hesaplanması

[pesimist2006]

arkadaşlar uzun bir aradan sonra tekrar forumda bulunuyorum ve yardımınıza ihtiyacım var.
ekte örnek tablomu hazırladım istediklerimi aşağıdaki gibi sıralıyorum.
1-sadece kayıt tarihi bitiş tarihi ve tutar kısımları doldurulacak
2-ve bu verileri girildiğinde toplam gün sayısı ve günlük tutar otomatik hesaplanacak
3-tarih aralığında hangi aya ait gün var ise o aya ait gün sayısı sütununa yazılacak (örnek 01.02.19 ve 31.03.2019 tarih aralığı verildi bu tarih aralığında 28 gün şubat ve 31 gün mart ayı var bu gün sayılarını otomatik ilgili sütuna yazılması)
4-bu gün sayılarını bulduktan sonra günlük ücret ile ilgili ayın gün sayısının çarpılarak o aya ait tutarın ilgili sütuna yazmak

kendimce anlatmaya çalıştım yardımcı olabilirseniz çok sevinirim.

örnek

 

[çıtır]

Dosyanız.

 

[YUSUF44]

G1 hücresine 01/01/2019 yazın ve hücre biçimini "aaaa" olarak ayarlayın

H1 hücresine

=SERİAY(G1;0)+1

Formülünü yazın ve sağa doğru aralık ayına kadar kopyalayın

U2 hücresine aşağıdaki formülü yazın ve sağa ve aşağı doğru kopyalayın:

=G2*$T2

Bundan sonraki kısmı maalesef formülle halledemedim. Makro ile çözmek için aşağıdaki kodları bir modüle kopyalayıp deneyin. Makro G:R aralığına belirttiğiniz şartlara göre gün sayılarını yazacaktır. Sağ taraftaki hesaplamaların doğru olması için makronun çalıştırılması gerekir:

Sub gunler()
son = Cells(Rows.Count, "D").End(3).Row
For i = 2 To son
    bas = Cells(i, "D")
    bit = Cells(i, "E")
    For ay = 7 To 18
        puan = 0
        For a = Cells(1, ay) To WorksheetFunction.EoMonth(Cells(1, ay), 0)
            If a >= bas And a <= bit Then
                puan = puan + 1
            End If
        Next
        Cells(i, ay) = puan
    Next
Next
End Sub

 

[pesimist2006]

Dosyanız.

altın üye olmadığım için maalesef dosyayı indiremiyorum

 

[çıtır]

Link ekledim.
https://www.dosyaupload.com/jgv0

 

[gicimi]

G1 hücresine 01/01/2019 yazın ve hücre biçimini "aaaa" olarak ayarlayın

H1 hücresine

=SERİAY(G1;0)+1

Formülünü yazın ve sağa doğru aralık ayına kadar kopyalayın

U2 hücresine aşağıdaki formülü yazın ve sağa ve aşağı doğru kopyalayın:

=G2*$T2

Bundan sonraki kısmı maalesef formülle halledemedim. Makro ile çözmek için aşağıdaki kodları bir modüle kopyalayıp deneyin. Makro G:R aralığına belirttiğiniz şartlara göre gün sayılarını yazacaktır. Sağ taraftaki hesaplamaların doğru olması için makronun çalıştırılması gerekir:

Teşekkürler.

 

[pesimist2006]

Link ekledim.
https://www.dosyaupload.com/jgv0

çok tşk ederim emeğinize sağlık nokta atışı olmuş

 

[çıtır]

çok tşk ederim emeğinize sağlık nokta atışı olmuş

Rica ederim.Dönüş yaptığınız için teşekkür ederim.

 

[pesimist2006]

Rica ederim.Dönüş yaptığınız için teşekkür ederim.

üstadım son olarak ay gününü 30 güne sabitleyerek tek ay olacak şekilde yapabilmemiz mümkünmüdür. yani 12 ay olmasına gerek yokda ay günü sütunu 30 gün olarak olacak şekilde

 

[çıtır]

Örnek dosya eklerseniz ve nasıl olması gerektiğini açıklarsanız yapmaya çalışırım.Açıklamanızı tam olarak anlamadım.
Örnek excel dosyası ekleme hakkında bilgi edinmek isterseniz: https://www.excel.web.tr/threads/soru-ile-ilgili-oernek-excel-dosyasi-ekleme.174755/ bakınız.

 

[pesimist2006]

Örnek dosya eklerseniz ve nasıl olması gerektiğini açıklarsanız yapmaya çalışırım.Açıklamanızı tam olarak anlamadım.
Örnek excel dosyası ekleme hakkında bilgi edinmek isterseniz: https://www.excel.web.tr/threads/soru-ile-ilgili-oernek-excel-dosyasi-ekleme.174755/ bakınız.

https://www.dosyaupload.com/jhaR

ütadım örnek dosya yukarıdaki gibi
yapmak istediğim tüm ayları 30 gün kabul edip toplamm ücreti ilgili aylara eşit şekilde bölüp otomatik ilgili hücreye yazdırmak

 

[Korhan Ayhan]

Merhaba,

Siz "G" sütununda normal hesaplama ile gün farkını bulmuşsunuz. Günlük tutarı da bu değere göre hesaplamışsınız.

Fakat aylık dağılımda 30 gün dikkate alınsın istemişsiniz. Bu durumda yıllık toplam sütunu ile "F" sütunundaki tutar tutmayacaktır.

Bence "G" sütununda GÜN360 fonksiyonu ile gün farkını bulun. Daha sonra J2 hücresine aşağıdaki formülü uygulayın.

G2; (Alt hücrelere sürüklein)

=GÜN360(D2;E2)

H2; (Alt hücrelere sürükleyin)

=EĞERHATA(F2/G2;0)

J2; (Yan ve alt hücrelere sürükleyin.

=$H2*EĞER(1*("1."&J$1&"."&YIL($D2))<$D2;0;EĞER(AY(TARİH(YIL($D2);AY(1*("1."&J$1&"."&YIL($D2)))+1;0))=2;EĞER(MAK(0;(MİN($E2;TARİH(YIL($D2);AY(1*("1."&J$1&"."&YIL($D2)))+1;0))-MAK($D2+1;1*("1."&J$1&"."&YIL($D2)))+1))=GÜN(TARİH(YIL($D2);AY(1*("1."&J$1&"."&YIL($D2)))+1;0));30;1);EĞER(MAK(0;(MİN($E2;TARİH(YIL($D2);AY(1*("1."&J$1&"."&YIL($D2)))+1;0))-MAK($D2+1;1*("1."&J$1&"."&YIL($D2)))+1))>30;30;MAK(0;(MİN($E2;TARİH(YIL($D2);AY(1*("1."&J$1&"."&YIL($D2)))+1;0))-MAK($D2+1;1*("1."&J$1&"."&YIL($D2)))+1)))))

Not: Formülün uzun olmasının sebebi ŞUBAT ayının 28-29 çekmek durumundan dolayıdır. Belki diğer arkadaşlar formülü daha kısaltabilir.

 

[çıtır]

Ayları sabit olarak 30 gün aldığınızda Ay sayısına göre G sütununda bulduğunuz günlerden 2, 3,4 gün fark oluşmakda H sütunda günlük ücretde değişmekdedir.Siz doğru olarak günleri ve günlük ücretleri tespit ediniz.Ben 30 aya eşitlenmiş ücret formülünü göndereyim.
Sayın Korhan AYHAN üstadım;
Toplam ücret tutarınız yukardaki açıklamam doğrultusunda 1 aydan fazla olan değerlerde yanlış çıkmakdadır.

 

[Korhan Ayhan]

Dediğim gibi GÜN360 formülü kullanılırsa aslında doğru sonuç veriyor. Fakat ŞUBAT ayı için kurduğum kurgudan dolayı fazla bir hesaplama yapıyor.

Önerdiğim formülü revize edeceğim.

 

[çıtır]

Benim bulduğum çözüm.Dosyanız.
https://www.dosyaupload.com/jhbV

 

[çıtır]

Dosya;
https://www.dosyaupload.com/jhbZ

 

[pesimist2006]

Dosya;
https://www.dosyaupload.com/jhbZ

üstadım olmuş elinize sağlık lakin kayıt tarihini 1.2.2019 veya şubat ayının herhangi bir günü girdiğimde 28 gün hesaplıyor bunların haricinde bir tarih girdiğimde şubat ayını 30 gün hesaplıyor

 

[çıtır]

Dosyanız.
https://www.dosyaupload.com/f541

 

[Korhan Ayhan]

Ben de #12 nolu mesajımdaki formülleri revize ettim. Dilerseniz denersiniz.

 

[çıtır]

Korhan Bey;
Sanırım 28.02.2019 başlıyanda Şubat 30 kabul edildiği için 3 gün çalışmış kabul edilemi.Tıpkı Şubat 28 çalışan 30 kabul edildiği gibi.Son çözüm dosyan sanırım istenen en doğru çözüm.Formül biraz uzadı.Daha kısa olabilir mi?Bilmiyorum.
https://www.dosyaupload.com/f553