• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

An equation to convert the Christmas calendar to the Coptic calendar

Hany ali

Member
Hello my dear professors, I would like to help you come up with an equation to onvert the Christmas calendar to the Coptic calendar as Data you found in Data Sheet
Consider On the basis that the Coptic calendar is lower than the Christmas calendar as 284 Years
 

Attachments

Last edited by a moderator:

Hany ali

Member
thank you mr PCI ,original date FROM Data Page from Upload Sheet
sorry mr.Vletm ,but your Result i don'tneed it ,just i want to convert date from cell B5 TO RESULT AS IN Cell C5
THANKS ALOT FOR ALL
this Result which, I want 23/Parmoute/1735
sorry,i want to change title to
Code:
equation to convert the Christmas calendar to the Coptic calendar
 

PCI

New Member
Here an UDF which could help
In a module put next code
Code:
Option Explicit
Function CopticDate(WkDate As Date) As String
Const YDiff = 284
Dim DateList  As Object
Set DateList = CreateObject("System.Collections.Sortedlist")
Dim T, TT
Dim I As Integer, J  As Integer
Dim AAA, BBB
Dim WkY  As Integer
Dim WkM  As String
Dim WkD As Integer

    With Sheets("Data")
        For I = 1 To 13
            T = Split(.Cells(I + 1, 2), "/")
            DateList.Add T(1) * 100 + T(0), .Cells(I + 1, 4)
        Next I
    End With
    WkY = Year(WkDate) - YDiff
    With DateList
        TT = Month(WkDate) * 100 + Day(WkDate)
        AAA = .GetKey(0)
        If (TT < .GetKey(0)) Then
            WkM = .GetKey(.Count)
        Else
            For I = 0 To 12
                If (TT < .GetKey(I)) Then
                    WkM = .GetByIndex(I - 1)
                    WkD = TT - .GetKey(I - 1)
                    Exit For
                End If
            Next I
        End If
    End With
    CopticDate = WkD & "/ " & WkM & "/ " & WkY

End Function
Assuming a date in B5 = Gregorian Date = 2019/05/01
Somewhere put =CopticDate(B5)

and check, check,check ...!
 

Attachments

Hany ali

Member
thanks alot,but i have some mistake in this code ,for example when i put this date 01/05/2019
i found the Result as
92/ Parmoute/ 1735
and as you know it's impossible
but the Result is 23/Parmoute/1735
 

PCI

New Member
thanks alot,but i have some mistake in this code ,for example when i put this date 01/05/2019
i found the Result as
92/ Parmoute/ 1735
and as you know it's impossible
but the Result is 23/Parmoute/1735
Yes I have seen some misaligments
I will investigate tomorrow
 

Hany ali

Member
thanks very much
I want gentlemen managers and supervisors to change the Title for this Post to
equation to convert the Christmas calendar to the Coptic calendar
 
Last edited by a moderator:

vletm

Excel Ninja
Hany ali
Hmm?
Your thread is ... or ... was 'An equation to convert the Coptic calendar to the Christmas calendar' - okay?
Did You use over one second to think something before You wrote ... something else?
If You'll think something and You'll write something ELSE
... then You'll get something ELSE ... not something that You wrote.
Should I feel lucky that I didn't use 'so-much-time' for this?
... much more than Your needed one second!
Please, use some of Your own time while writing ... something.
Please, reread Forum Rules ?
And for those who would continue:
 

Hany ali

Member
I apologize and regret you for this big problem that I made, which made me not even forgive myself for this mistake because the first time I do such
But I promise that it will never be repeated in the future
I hope that you please forgive me and forgive my sin
 

PCI

New Member
See remake
Code:
Option Explicit
Function CopticDate(WkDate As Date) As String
Const YDiff = 284
Dim DateList  As Object
Set DateList = CreateObject("System.Collections.Sortedlist")
Dim T, TT
Dim I As Integer, II  As Integer
Dim WkY  As Integer
Dim WkM  As String
Dim WkD As Integer

    With Sheets("Data")
        For I = 1 To 13
            T = Split(.Cells(I + 1, 2), "/")
            DateList.Add T(1) * 100 + T(0), .Cells(I + 1, 4)
        Next I
    End With
    WkY = Year(WkDate) - YDiff
    With DateList
        TT = Month(WkDate) * 100 + Day(WkDate)
        If (TT >= .GetKey(.Count - 1)) Then
            WkM = .GetByIndex(0)
            WkD = TT - .GetKey(.Count - 1) + 1
        Else
            If (TT < .GetKey(0)) Then
                WkM = .GetByIndex(.Count - 1)
                II = TT - 101                                 '  FIRST day of the year = 101
                WkD = 1231 - .GetKey(.Count - 1) + II         '  LAST  day of the year = 1231
                      
            Else
                For I = 0 To 12
                    If (TT < .GetKey(I)) Then
                        WkM = .GetByIndex(I - 1)
                        WkD = TT - .GetKey(I - 1) + 1
                        Exit For
                    End If
                Next I
            End If
        End If
    End With
    CopticDate = WkD & "/ " & WkM & "/ " & WkY

End Function
d Function
 

Hany ali

Member
please try ...still this result for 01/05/2019 and mistake for all Results
93/ Parmoute/ 1735
 
Last edited:

PCI

New Member
Same player shot again

Code:
Option Explicit
Function CopticDate(WkDate As Date) As String
Const YDiff = 284
Dim DateList  As Object
Set DateList = CreateObject("System.Collections.Sortedlist")
Dim T, TT
Dim I As Integer, II  As Integer
Dim WkY  As Integer
Dim WkM  As String
Dim WkD As Integer
Dim AAA, BBB, CCC, DDD

    With Sheets("Data")
        For I = 1 To 13
            T = Split(.Cells(I + 1, 3), "/")
            DateList.Add DateSerial(Year(WkDate), T(1), T(0)) * 1, .Cells(I + 1, 4)
        Next I
    End With
    WkY = Year(WkDate) - YDiff
    With DateList
        TT = WkDate * 1
        If (TT >= .GetKey(.Count - 1)) Then
            WkM = .GetByIndex(0)
            WkD = TT - .GetKey(.Count - 1) + 1
        Else
            If (TT <= .GetKey(0)) Then
                WkM = .GetByIndex(.Count - 1)
                II = TT - DateSerial(Year(WkDate), 1, 1)           '  FIRST day of the year = 101
                WkD = DateSerial(Year(WkDate), 12, 31) - .GetKey(.Count - 1) + II        '  LAST  day of the year = 1231
                      
            Else
                For I = 0 To 12
                    AAA = .GetKey(I)
                    BBB = .GetKey(I + 1)
                    If ((TT > .GetKey(I)) And (TT <= .GetKey(I + 1))) Then
                        WkM = .GetByIndex(I + 1)
                        WkD = TT - .GetKey(I)
                        Exit For
                    End If
                Next I
            End If
        End If
    End With
    CopticDate = WkD & "/ " & WkM & "/ " & WkY

End Function
 

Hany ali

Member
Bravo - This is the least of the so-called you that you are creative
Thank you very much for your presence and very sorry for your tiredness
This is already required and these are the desired results
 

PCI

New Member
Ouf We got it.
In fact, at the end it seems quite simple but it took me to do a lot of mistakes ...

Last code after cleaning
Code:
Option Explicit
Function CopticDate(WkDate As Date) As String
Const YDiff = 284
Dim DateList  As Object
Set DateList = CreateObject("System.Collections.Sortedlist")
Dim T, TT
Dim I As Integer, II  As Integer
Dim WkY  As Integer
Dim WkM  As String
Dim WkD As Integer

    With Sheets("Data")
        For I = 1 To 13
            T = Split(.Cells(I + 1, 3), "/")
            DateList.Add DateSerial(Year(WkDate), T(1), T(0)) * 1, .Cells(I + 1, 4)
        Next I
    End With
    WkY = Year(WkDate) - YDiff
    With DateList
        TT = WkDate * 1
        If (TT >= .GetKey(.Count - 1)) Then
            WkM = .GetByIndex(0)
            WkD = TT - .GetKey(.Count - 1) + 1
        Else
            If (TT <= .GetKey(0)) Then
                WkM = .GetByIndex(.Count - 1)
                II = TT - DateSerial(Year(WkDate), 1, 1)           '  FIRST day of the year = 101
                WkD = DateSerial(Year(WkDate), 12, 31) - .GetKey(.Count - 1) + II        '  LAST  day of the year = 1231
                     
            Else
                For I = 0 To 12
                    If ((TT > .GetKey(I)) And (TT <= .GetKey(I + 1))) Then
                        WkM = .GetByIndex(I + 1)
                        WkD = TT - .GetKey(I)
                        Exit For
                    End If
                Next I
            End If
        End If
    End With
    CopticDate = WkD & "/ " & WkM & "/ " & WkY

End Function
 
Last edited by a moderator:
Top