英文:
this is how to calculate dates in excel using functions
问题
在 2017 年，伊斯兰斋月拉马丹于周五，5月26日开始。这占一年的多少分数？我手动计算为 146/365，但我正在寻找一个Excel公式来计算它。
英文:
In 2017 the Islamic month of fasting, Ramadan, began on Friday, 26 May. What fraction of the year was this? I calculated it manually as 146/365, but I am looking for an excel formula to calculate it.
答案1
得分: 2
If you have the date when Ramadan began on Cell A1, then:
=A1 - DATE(YEAR(A1),1,0)
or
=A1 - DATE(YEAR(A1),1,1)+1
will give you the Ramadan day of the year, to get the fraction:
=(A1 - DATE(YEAR(A1),1,0))/(DATE(YEAR(A1),12,31) - DATE(YEAR(A1),1,1)+1)
or using LET to avoid repetition:
=LET(r, A1, y, YEAR(r), (r-DATE(y,1,0))/(DATE(y,12,31) - DATE(y,1,1)+1))
You can replace the denominator using a leap year condition as follows:
=LET(r, A1, y, YEAR(A1), (r-DATE(y,1,0))/(365+IF(MONTH(DATE(y,2,29))=2,1,0)))
英文:
If you have the date when Ramadan began on Cell A1, then:
=A1 - DATE(YEAR(A1),1,0)
or
=A1 - DATE(YEAR(A1),1,1)+1
will give you the Ramadan day of the year, to get the fraction:
=(A1 - DATE(YEAR(A1),1,0))/(DATE(YEAR(A1),12,31) - DATE(YEAR(A1),1,1)+1)
or using LET to avoid repetition:
=LET(r, A1, y, YEAR(r), (r-DATE(y,1,0))/(DATE(y,12,31) - DATE(y,1,1)+1))
You can replace the denominator using a leap year condition as follows:
=LET(r, A1, y, YEAR(A1), (r-DATE(y,1,0))/(365+IF(MONTH(DATE(y,2,29))=2,1,0)))
答案2
得分: 0
=YEARFRAC(start_date,end_date)
其中，你的 start_date：1/1/2017，和你的 end_date：5/26/2017
英文:
You can use:
=YEARFRAC(start_date,end_date)
whereby, your start_date: 1/1/2017, and your end_date: 5/26/2017
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论