Rumus Excel penentuan jadwal imsakiyah Ramadhan

Disusun oleh Zaenal Mustofa (Mahasiswa UIN Walisongo)

Mencari Koordinat Kab Batang
Lintang Tempat (LT) =J+M/60+D/3600
Bujur Tempat (BT) =J+M/60+D/3600
Waktu Dhuha (WD) =J+M/60+D/3600
10 menit (1M) =J+M/60+D/3600
Menentukan Deklinasi Matahari (DM)=J+M/60+D/3600
Menentukan Equation Of Time (ET)=J+M/60+D/3600
Menentukan h0 Ashar =DEGREES(ATAN(1/(TAN(RADIANS(DM-LT))+1)))
DATA 1 (Sheet 1)
Menentukan Waktu Sholat
Waktu Imsak
=subuh-1M
Sudut waktu imsak =IF(imsak<0;"-";"")&TEXT(ABS(TRUNC(imsak));"00")&"° "&TEXT(ABS(TRUNC((imsak-TRUNC(imsak))*60));"00")&"' "&TEXT(ABS(((((imsak-(TRUNC(imsak)))*60)-(TRUNC(((imsak-(TRUNC(imsak)))*60))))*60));"00,00")&""""
Waktu subuh
=12-(DEGREES(ACOS(SIN(RADIANS(20))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-ET+((105-BT)/15)
Sudut waktu subuh
=IF(subuh<0;"-";"")&TEXT(ABS(TRUNC(subuh));"00")&"° "&TEXT(ABS(TRUNC((subuh-TRUNC(subuh))*60));"00")&"' "&TEXT(ABS(((((subuh-(TRUNC(subuh)))*60)-(TRUNC(((subuh-(TRUNC(subuh)))*60))))*60));"00,00")&""""
Waktu Terbit
=12-(DEGREES(ACOS(SIN(RADIANS(-1))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-ET+((105-BT)/15)
Sudut Waktu Terbit
=IF(terbit<0;"-";"")&TEXT(ABS(TRUNC(terbit));"00")&"° "&TEXT(ABS(TRUNC((terbit -TRUNC(terbit))*60));"00")&"' "&TEXT(ABS(((((terbit -(TRUNC(terbit)))*60)-(TRUNC(((terbit -(TRUNC(terbit)))*60))))*60));"00,00")&""""
Waktu Dhuha
=12-(DEGREES(ACOS(SIN(RADIANS(4,5))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-LT+((105BT)/15)
Sudut Waktu Dhuha
=IF(dhuha<0;"-";"")&TEXT(ABS(TRUNC(dhuha));"00")&"° "&TEXT(ABS(TRUNC((dhuha -TRUNC(dhuha))*60));"00")&"' "&TEXT(ABS(((((dhuha -(TRUNC(dhuha)))*60)-(TRUNC(((dhuha -(TRUNC(dhuha)))*60))))*60));"00,00")&""""
Waktu Dhuhur
=12-ET+((105-BT)/15)
Sudut Waktu Dhuhur
=IF(dhuhur<0;"-";"")&TEXT(ABS(TRUNC(dhuhur));"00")&"° "&TEXT(ABS(TRUNC((dhuhur -TRUNC(dhuhur))*60));"00")&"' "&TEXT(ABS(((((dhuhur -(TRUNC(dhuhur)))*60)-(TRUNC(((dhuhur -(TRUNC(dhuhur)))*60))))*60));"00,00")&""""
Waktu Ashar
=12+(DEGREES(ACOS(SIN(RADIANS(ho Ashar))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-ET+((105-BT)/15)
Sudut Waktu Ashar
=IF(Ashar<0;"-";"")&TEXT(ABS(TRUNC(Ashar));"00")&"° "&TEXT(ABS(TRUNC((Ashar -TRUNC(Ashar))*60));"00")&"' "&TEXT(ABS(((((Ashar -(TRUNC(Ashar)))*60)-(TRUNC(((Ashar -(TRUNC(Ashar)))*60))))*60));"00,00")&""""
Waktu Maghrib
=12+(DEGREES(ACOS(SIN(RADIANS(-1))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-ET+((105-BT)/15)
Sudut Waktu Maghrib
=IF(maghrib<0;"-";"")&TEXT(ABS(TRUNC(maghrib));"00")&"° "&TEXT(ABS(TRUNC((maghrib -TRUNC(maghrib))*60));"00")&"' "&TEXT(ABS(((((maghrib -(TRUNC(maghrib)))*60)-(TRUNC(((maghrib -(TRUNC(maghrib)))*60))))*60));"00,00")&""""
Waktu Isya’
=12+(DEGREES(ACOS(SIN(RADIANS(-18))/COS(RADIANS(LT))/COS(RADIANS(DM))-TAN(RADIANS(LT))*TAN(RADIANS(DM))))/15)-ET+((105-BT)/15)
Sudut Waktu Isya’
=IF(isya<0;"-";"")&TEXT(ABS(TRUNC(isya));"00")&"° "&TEXT(ABS(TRUNC((isya -TRUNC(isya))*60));"00")&"' "&TEXT(ABS(((((isya -(TRUNC(isya)))*60)-(TRUNC(((isya -(TRUNC(isya)))*60))))*60));"00,00")&""""
DATA 2 (Sheet 2)
Waktu Imsak
Kolom 1
=Sheet1imsak
Kolom 2
 =IF(kolom1<0;"-";"")&TEXT(ABS(TRUNC(kolom1));"00")&": "&TEXT(ABS(TRUNC((kolom1-TRUNC(kolom1))*60));"00")&": "&TEXT(ABS(((((kolom1-(TRUNC(kolom1)))*60)-(TRUNC(((kolom1-(TRUNC(kolom1)))*60))))*60));"00,00")
Kolom 3 =IF(kolom1<0;"";"")&TEXT(ABS(TRUNC(kolom1));"00")&":"&TEXT(ABS(TRUNC((kolom1-TRUNC(kolom1))*60));"00")+IF(TEXT(ABS(((((kolom1-(TRUNC(kolom1)))*60)-(TRUNC(((kolom1-(TRUNC(kolom1)))*60))))*60));"00,00")>1;1)+2
Waktu subuh
Kolom 1
=Sheet1subuh
Kolom 2
=IF(Kolom 1<0;"-";"")&TEXT(ABS(TRUNC(Kolom 1));"00")&": "&TEXT(ABS(TRUNC((Kolom 1-TRUNC(Kolom 1))*60));"00")&": "&TEXT(ABS(((((Kolom 1-(TRUNC(Kolom 1)))*60)-(TRUNC(((Kolom 1-(TRUNC(Kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(Kolom 1<0;"-";"")&TEXT(ABS(TRUNC(Kolom 1));"00")&":"&TEXT(ABS(TRUNC((Kolom 1-TRUNC(Kolom 1))*60));"00")+IF(TEXT(ABS(((((Kolom 1-(TRUNC(Kolom 1)))*60)-(TRUNC(((Kolom 1-(TRUNC(Kolom 1)))*60))))*60));"00,00")>1;1)+2
 Waktu Terbit
Kolom 1
=Sheet1terbit
Kolom 2
=IF(Kolom 1<0;"-";"")&TEXT(ABS(TRUNC(Kolom 1));"00")&": "&TEXT(ABS(TRUNC((Kolom 1-TRUNC(Kolom 1))*60));"00")&": "&TEXT(ABS(((((Kolom 1-(TRUNC(Kolom 1)))*60)-(TRUNC(((Kolom 1-(TRUNC(Kolom 1)))*60))))*60));"00,00")
Kolom 3
  =IF(Kolom 1<0;"-";"")&TEXT(ABS(TRUNC(Kolom 1));"00")&":"&TEXT(ABS(TRUNC((Kolom 1-TRUNC(Kolom 1))*60));"00")+IF(TEXT(ABS(((((Kolom 1-(TRUNC(Kolom 1)))*60)-(TRUNC(((Kolom 1-(TRUNC(Kolom 1)))*60))))*60));"00,00")>1;0)-2
Waktu Dhuha
Kolom 1
=Sheet1dhuha
Kolom 2
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&": "&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")&": "&TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&":"&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")+IF(TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")>1;1)+2
Waktu Dhuhur
Kolom 1
=Sheet1Dhuhur
Kolom 2
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&": "&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")&": "&TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&":"&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")+IF(TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")>1;1)+3
Waktu Ashar
Kolom 1
=Sheet1Ashar
Kolom 2
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&": "&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")&": "&TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&":"&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")+IF(TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")>1;1)+2
Waktu Maghrib
Kolom 1
=Sheet1Maghrib
Kolom 2
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&": "&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")&": "&TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&":"&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")+IF(TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")>1;1)+2
Waktu Isya’
Kolom 1
=Sheet1isya’
Kolom 2
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&": "&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")&": "&TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")
Kolom 3
=IF(kolom 1<0;"-";"")&TEXT(ABS(TRUNC(kolom 1));"00")&":"&TEXT(ABS(TRUNC((kolom 1-TRUNC(kolom 1))*60));"00")+IF(TEXT(ABS(((((kolom 1-(TRUNC(kolom 1)))*60)-(TRUNC(((kolom 1-(TRUNC(kolom 1)))*60))))*60));"00,00")>1;1)+2