# 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

• 15.8 KB Views: 7
Last edited by a moderator:

#### PCI

##### New Member
Please I Want This Result As Formula From Data Page
OK but from which original date ???

#### vletm

##### Excel Ninja
Hany ali
With this, You could solve SOME results within those Your given dates.
Result is in cell B10.

#### Attachments

• 16.6 KB Views: 5

#### 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

• 27.7 KB Views: 4

#### 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!
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
mistake for all Results
What sort of mistakes ...?

#### Hany ali

##### Member
as you see in Calculate days ,for Example in Date 01/05/2019
it should be 23/Parmoute/1735
Not 93/ Parmoute/ 1735
As contained in that link :-

#### 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:

#### Hany ali

##### Member
Thanks alot my sir