if cell value is hijri or gregorian date?

I think you'll need to post a sample workbook with examples of the dates ... ideally, all the months.

I suspect that a Gregorian date will show up as a number and a hijri date might be text but that's just a guess.


Regards, TMS

I found the following code in an Access Forum and it works, unchanged, in Excel 2007.

' http://www.access-programmers.co.uk/forums/showthread.php?t=246559
' hafeezksa

' Hijri (Arabic) date to Gregorian date converter module ...

'------------------------------------------
Option Compare Binary
Option Explicit
' Constants from olenls.h
Private Const LOCALE_ICALENDARTYPE = &H1009 ' /* type of calendar specifier */
Private Const CAL_HIJRI = 6 ' /* Hijri (Arabic Lunar) calendar */
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal lcid As Long, ByVal LCTYPE As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Private m_iHijri As Integer
'------------------------------------------------------------
' StDteGregOfStDteHijri
'------------------------------------------------------------
Public Function StDteGregOfStDteHijri(ByVal stDateHijri As String) As String
On Error Resume Next
Dim dte As Date

If Len(stDateHijri) > 0 Then
VBA.Calendar = vbCalHijri
dte = CDate(stDateHijri)
VBA.Calendar = vbCalGreg
StDteGregOfStDteHijri = CStr(dte)
End If

If Err.Number <> 0 Then StDteGregOfStDteHijri = stDateHijri
End Function
'------------------------------------------------------------
' StDteHijriOfStDteGreg
'------------------------------------------------------------
Public Function StDteHijriOfStDteGreg(ByVal stDateGreg As String) As String
On Error Resume Next
Dim dte As Date

If Len(stDateGreg) > 0 Then
dte = CDate(stDateGreg)
VBA.Calendar = vbCalHijri
StDteHijriOfStDteGreg = CStr(dte)
VBA.Calendar = vbCalGreg
End If

If Err.Number <> 0 Then StDteHijriOfStDteGreg = stDateGreg
End Function
'-----------------------------------------------------
' FHijriCalendar
'-----------------------------------------------------
Public Property Get FHijriCalendar() As Boolean
Dim stCal As String

If m_iHijri = 0 Then
stCal = StGetLocaleInfo(LOCALE_ICALENDARTYPE, False)
If (Val(stCal) = CAL_HIJRI) Then
m_iHijri = 1
Else
m_iHijri = 2
End If
End If

FHijriCalendar = (m_iHijri = 1)
End Property
'----------------------------------------------------------------------
' StGetLocaleInfo
'
' Gets Locale (international) info about current config
' See LOCALE constants at top of module for LCTYPE values
'----------------------------------------------------------------------
Public Function StGetLocaleInfo(LCTYPE As Long, Optional fUserDefault As Boolean = True) As String
Dim lcid As Long
Dim stBuff As String * 255

'Get current language ID
If fUserDefault Then
lcid = GetUserDefaultLCID()
Else
lcid = GetSystemDefaultLCID()
End If

'ask for the locale info
If (GetLocaleInfo(lcid, LCTYPE, ByVal stBuff, Len(stBuff)) > 0) Then
StGetLocaleInfo = StFromSz(stBuff)
End If
End Function
'------------------------------------------------------------
' StFromSz
'
' Find the first vbNullChar in a string, and return
' everything prior to that character. Extremely
' useful when combined with the Windows API function calls.
'------------------------------------------------------------
Public Function StFromSz(ByVal sz As String) As String

Dim ich As Integer

ich = InStr(sz, vbNullChar)
Select Case ich
' It's best to put the most likely case first.
Case Is > 1
' Found in the string, so return the portion
' up to the null character.
StFromSz = Left$(sz, ich - 1)
Case 0
' Not found at all, so just
' return the original value.
StFromSz = sz
Case 1
' Found at the first position, so return an empty string.
StFromSz = vbNullString
End Select
End Function

Assuming that the calculation is accurate (which I can't really check), this should meet your needs.

See the attached updated sample workbook.

Regards, TMS

For your formula, you could use:

Formula:

=IFERROR(INT((TODAY()-C2)/365.25),"hijri date")

Or, for a more accurate calculation (text string output):

Formula:

=IFERROR(DATEDIF(C2,TODAY(),"Y")&" Years, "&DATEDIF(C2,TODAY(),"YM")&

" Months, "&DATEDIF(C2,TODAY(),"MD")&" Days","hijri date")

	A	B	C	D	E	F
1	EMP_NAME	FILENO	BIRTHDATE	AGE
2	Employee 1	546	03/02/1977	37	37 Years, 11 Months, 4 Days
3	Employee 2	858	1/7/1397	hijri date	hijri date	error because date is hijri/arabic
4	Employee 3	549	30/8/1978	36	36 Years, 4 Months, 8 Days
5	Employee 4	554	03/02/1978	36	36 Years, 11 Months, 4 Days
6	Employee 1	770	19/11/1978	36	36 Years, 1 Months, 19 Days
7	Employee 1	638	15/10/1979	35	35 Years, 2 Months, 23 Days
8	Employee 1	764	13/2/1979	35	35 Years, 10 Months, 25 Days
9	Employee 1	843	20/2/1399	hijri date	hijri date	error because date is hijri/arabic
10	Employee 1	310	2/6/1400	hijri date	hijri date	error because date is hijri/arabic
11	Employee 1	568	23/1/1980	34	34 Years, 11 Months, 15 Days
12	Employee 1	804	06/04/1980	34	34 Years, 9 Months, 1 Days
13
14
15					D2:	=IFERROR(INT((TODAY()-C2)/365.25),"hijri date")
16					E2:	=IFERROR(DATEDIF(C2,TODAY(),"Y")&" Years, "&DATEDIF(C2,TODAY(),"YM")& " Months, "&DATEDIF(C2,TODAY(),"MD")&" Days","hijri date")
17

Regards, TMS

Having read the other thread in more detail, I understand that the converting from hijri dates to Gregorian dates can "sometimes/normally" be a day out.

However, there were reports that the calculation could be two days out.

Don't know if this is a big deal or not.

Regards, TMS