Bir Tuhaf Tarih Formülü

[jander06]

Arkadaşlar hepinize günaydın.
Excel listemde yer alan tarihleri;
Muhasebede işlemler on (10) ar günlük kayıt edilmekte. ayın 1 i ile 10 arasında olan tüm tarihleri ayın 10 u olarak,
ayın 11 ile 20 si arasında olan tüm tarihleri ayın 20 si olarak,
ayın 21 ile 30 u arasında olan tüm tarihleri ayın 30 u olarak ve
ayın 31 lerinide 31 olarak değiştirmek istemekteyim.

Yardımlarınız için minnettarım, teşekkür ederim.

 

[YUSUF44]

B1'e aşağıdaki formülü girerseniz A1'deki tarihi istediğiniz şarta göre B1 hücresinde dönüştürebilirsiniz:

=TARİH(YIL(A1);AY(A1);ARA(GÜN(A1);{1;11;21;31\10;20;30;31}))

 

[jander06]

Çok ama çok teşekkür ederim...

 

[jander06]

Yusuf hocam peki A1 hücresi boş iken bu formülde #YOK hatası alıyorum. Bunu nasıl engelleyebilirim..?

 

[Emir Hüseyin Çoban]

Yusuf hocam peki A1 hücresi boş iken bu formülde #YOK hatası alıyorum. Bunu nasıl engelleyebilirim..?

. . .

Forumda anahtar kelimelerle arama yaparsanız bir çok çözüme ulaşırsınız.

En basit haliyle

[COLOR="Blue"]=EĞER(A1="";"";[/COLOR]TARİH(YIL(A1);AY(A1);ARA(GÜN(A1);{1;11;21;31\10;20;30;31}))[COLOR="Blue"])[/COLOR]

. . .

 

[jander06]

Çok teşekkürler...

 

[YUSUF44]

Makroyla çözüm için aşağıdaki kodları oluşturdum ancak maalesef { karakteri için invalid uyarısı verdi. Nasıl düzelteceğimi de bulamadım

Sub Tarih()
For i = 1 To Cells(Rows.Count, 1).End(3).Row
    Cells(i, 1) = Dateserial(year(cells(i,1)), month(cells(i,1)), worksheetfunction.lookup(day(cells(i,1)),[B]{[/B]1,11,21,31;10,20,30,31[B]}[/B])
Next
End Sub

 

[YUSUF44]

Şu şekilde oldu:

Sub Tarih()
For i = 1 To Cells(Rows.Count, 1).End(3).Row
If Cells(i, 1) <> "" And IsDate(Cells(i, 1)) = True Then
    Cells(i, 1) = DateSerial(Year(Cells(i, 1)), Month(Cells(i, 1)), WorksheetFunction.Min(WorksheetFunction.Ceiling(Day(Cells(i, 1)), 10), 31))
End If
Next
End Sub

Ancak hala ilk kodun neden olmadığını, { yerine ne kullanılması gerektiğini merak ediyorum.

 

[YUSUF44]

Şubat ayını da dikkate alırsak aşağıdaki kodlar daha uygun oldu sanki:

Sub Tarih()
For i = 1 To Cells(Rows.Count, 1).End(3).Row
If Cells(i, 1) <> "" And IsDate(Cells(i, 1)) = True Then
    Cells(i, 1) = DateSerial(Year(Cells(i, 1)), Month(Cells(i, 1)), WorksheetFunction.Min(WorksheetFunction.Ceiling(Day(Cells(i, 1)), 10), Day(WorksheetFunction.EoMonth(Cells(i, 1), 0))))
End If
Next
End Sub

 

[jander06]

Yusuf bey mükemmel oldu çok teşekkür ederim..

 

[Emir Hüseyin Çoban]

Makroyla çözüm için aşağıdaki kodları oluşturdum ancak maalesef { karakteri için invalid uyarısı verdi. Nasıl düzelteceğimi de bulamadım

Sub Tarih()
For i = 1 To Cells(Rows.Count, 1).End(3).Row
    Cells(i, 1) = Dateserial(year(cells(i,1)), month(cells(i,1)), worksheetfunction.lookup(day(cells(i,1)),[B]{[/B]1,11,21,31;10,20,30,31[B]}[/B])
Next
End Sub

. . .

Sub Kod()
    For i = 1 To Cells(Rows.Count, 1).End(3).Row
      [COLOR="Green"]  'Cells(i, 1) = Dateserial(year(cells(i,1)), month(cells(i,1)), worksheetfunction.lookup(day(cells(i,1)),{1,11,21,31;10,20,30,31})[/COLOR]
        Cells(i, 1) = Evaluate("=DATE(YEAR(A" & i & "),MONTH(A" & i & "),LOOKUP(DAY(A" & i & "),{1,11,21,31;10,20,30,31}))")
    Next
End Sub

. . .

 

[Muzaffer Ali]

{} işaretleri arasında kalan değerler dizi değerlerdir {} ve arasındaki değerler için kodlama bölümüne dizi değişken yazılabilir. Yeni nesil programlama dillerinde direk diziye atanabilir.

aşağıdaki gibi

dim dizi() as integer

dizi={1,11,21,31;10,20,30,31}

ama excel buna izin vermez.

Bildiğimiz şekilde dizi tanımlaması yapılmalıdır

yani verdiğiniz kodlar aşağıdaki gibi değiştirilmelidir.

Sub Tarih()
    Dim Dizi(3, 1) As Integer
    Dizi(0, 0) = 1
    Dizi(0, 1) = 10
    Dizi(1, 0) = 11
    Dizi(1, 1) = 20
    Dizi(2, 0) = 21
    Dizi(2, 1) = 30
    Dizi(3, 0) = 31
    Dizi(3, 1) = 31
    
    For i = 1 To Cells(Rows.Count, 1).End(3).Row
        Cells(i, 2) = DateSerial(Year(Cells(i, 1)), Month(Cells(i, 1)), WorksheetFunction.Lookup(Day(Cells(i, 1)), Dizi))
    Next
End Sub

Kolay gelsin.

 

[YUSUF44]

Sayın Hüseyin Çoban ve dalgalıkur, çok çok teşekkürler. Öğrendiğim çok iyi oldu.

Sayın Hüseyin ÇOBAN'ın kodlarındaki evaluate, hücreye formül girmeden formülü çalıştırıyor sanıyorum.

Sayın dalgalıkur, dizi oluşturmanın mantığını biraz anlatır mısınız? Daha basit olabilir mi, 3 ve 1'in anlamı nedir?

 

[Erdem Akdemir]

Yusuf bey merhaba,

Bildiğim kadarıyla söylüyorum, 3'ü satır gibi düşünün,1'i ise sütun gibi.

Vba diziler 0'dan başladığı için, 4 satır ve iki sütunlu bir dizi oluşmuş.

 

[Erdem Akdemir]

Tek dizi değil ama iki dizi ile olursa aşağıdaki çözümde olabilir.

Sub Tarih3()
    Dizi1 = Array(1, 11, 21, 31)
    dizi2 = Array(10, 20, 30, 31)
    
    [c3] = WorksheetFunction.Lookup(Day([a1]), Dizi1, dizi2)
   
End Sub

 

[YUSUF44]

Sayın kuvari çok teşekkürler, şimdi daha iyi anladım.

 

[Muzaffer Ali]

Sayın dalgalıkur, dizi oluşturmanın mantığını biraz anlatır mısınız? Daha basit olabilir mi, 3 ve 1'in anlamı nedir?

#Kuvari açıklamış ama ek bilgi olsun

Dim Dizi(3, 1) As Integer '4 elemanlı bir dizidir her elemanın 2 alt elemanı vardır
Dizi(0, 0) = 1 'birinci elemanın, birinci alt eleman değeri=1 dir
Dizi(0, 1) = 10 'birinci elemanın, ikinci alt eleman değeri=10 dur

Dizi(1, 0) = 11 'ikinci elemanın, birinci alt eleman değeri=11 dir
Dizi(1, 1) = 20 'ikinci elemanın, ikinci alt eleman değeri=20 dir
Diğerleride aynı şekilde
Dizi(2, 0) = 21
Dizi(2, 1) = 30

Dizi(3, 0) = 31
Dizi(3, 1) = 31

Kolay gelsin

 

[YUSUF44]

Teşekkürler. Bu diziyi excelde hücrelere yazınca daha iyi anladım.