how to set an excel formula to calculate
number of days including am and pm ?My basic requirement is:
i need to create an excel file to input start date and end date (including to indicate am or pm), in order to calculate the number of
days an employee taking sick leave or vacation leave
in between different dates .I input start date of sick leave in column A , end date of sick leave into column B and need to set a formula in Column C to show the actual number of days of sick leave the employee had taken. 我已找到可計算日期之間的相差日數的website, 但no use for me because i still need to input some other information and date into the excel file in another Column , therefore i need the formula to insert into my excel file, not just know the 日期之間的相差日數.My basic requirement :
1. Counting within the same day:
from a.m to a.m is 0.5 day (the same day)
from p.m to p.m is 0.5 day (the same day) For example:from : 1 oct 2011 a.m to 1 oct 2011 a.m is 0.5 day
from: 1 oct 2011 p.m to 1 oct 2011 p.m is 0.5 day
from: 1 oct 2011 a.m to 1 oct 2011 p.m is 1 day
From a.m. to p.m is 1 day (the same date)
2. Counting from today to the next date or more dates :
from today a.m to tomorrow a.m is 1.5 day ( from one day to another date)
from today a.m to tomorrow p.m is 2 days
for example:From 1 oct 2011 a.m to 2 oct 2011 a.m is 1.5 day
from : 1 oct 2011 a.m. to 2 oct 2011 pm is 2 days
from: 1 oct 2011 a,m. to 3 oct 2011 am is 2.5 days
from : 1 oct 2011 a.m. to 3 oct 2011 pm is 3 days
如此類推....
請高人provide formula.
2. If no formula, which website i can go to get help?
3 any free software i can get ?
尋找電腦高手--excel formula , urgent
2011-10-16 9:12 pm
回答 (5)
2011-10-20 8:56 pm
✔ 最佳答案
Suppose A1 contains "From", B1 contains "am/pm",C1 contains "To" and D1 contains "am/pm".
That is to say, we use four columns to record the start-date and end-dates.
Now enter your trial data in row 2.
In E2, enter
=C2-A2+0.5*(AND(B2="am",D2="am"))+1*(AND(B2="am",D2="pm"))+0.5*(AND(B2="pm",D2="pm"))
The number of leave days will appear in E2.
Hope you enjoy the formula.
2011-10-18 12:10 am
如果你只單靠AM同PM去決定半天或一天的話, 公式便會以文字形的方式去計算, 這並不是太方便處理...而且不知你原來在A行及B行所輸入的內容是甚麼 ?而最好你在A及B行都加上時間作為分界線, 方法是 : 輪入"月/日 +空格 + 時:分", 以9:00為上午上班, 13:00為上午的終止時間, 14:00為下午上班, 18:00為下午下班時間.要顯示AM/PM, 可於格式設定時用自定 : dd/mm/yyyy h:mm AM/PM
如不想顯示時間, 可改成dd/mm/yyyy AM/PM, 就會變成你問題中所說的格式, 以下為C1對應B1及A1的公式.因不想公式太長, 所以不作錯誤檢測 (如B1細過A1會出現錯誤值), 亦只將4個鐘及以下作半天計算, 多過4個鐘作一天計.(如需補充請提供更詳細資料)=TEXT(IF(HOUR(B1-A1)<=4,DAY(B1-A1)+0.5,DAY(B1-A1)+1),"#0.0")&" days"
10/01/2011 9:00 AM10/01/2011 1:00 PM0.5 days10/01/2011 2:00 PM10/01/2011 6:00 PM0.5 days10/01/2011 9:00 AM10/01/2011 6:00 PM1.0 days 10/01/2011 9:00 AM10/02/2011 1:00 PM1.5 days10/01/2011 9:00 AM10/02/2011 6:00 PM2.0 days10/01/2011 9:00 AM10/03/2011 1:00 PM2.5 days10/01/2011 9:00 AM10/03/2011 6:00 PM3.0 days
2011-10-18 17:07:04 補充：
以下是美式日期顯示, 如果你用英式以日行先的話請自行修正
mm-dd HH:MM (英式 : dd-mm HH:MM)
A1 column 輸入 10/01/2011 2 PM 應輸入為 10-1 14:00 (日期同時間中間留一空格)
B1 column 輸入 10/01/2011 6 PM 應輸入為 10-1 18:00
這樣應會成功.
記得格式設定時用自定 : dd/mm/yyyy h:mm AM/PM
如不想顯示時間, 可改成dd/mm/yyyy AM/PM, 就會變成你問題中所說的格式, 以下為C1對應B1及A1的公式.因不想公式太長, 所以不作錯誤檢測 (如B1細過A1會出現錯誤值), 亦只將4個鐘及以下作半天計算, 多過4個鐘作一天計.(如需補充請提供更詳細資料)=TEXT(IF(HOUR(B1-A1)<=4,DAY(B1-A1)+0.5,DAY(B1-A1)+1),"#0.0")&" days"
10/01/2011 9:00 AM10/01/2011 1:00 PM0.5 days10/01/2011 2:00 PM10/01/2011 6:00 PM0.5 days10/01/2011 9:00 AM10/01/2011 6:00 PM1.0 days 10/01/2011 9:00 AM10/02/2011 1:00 PM1.5 days10/01/2011 9:00 AM10/02/2011 6:00 PM2.0 days10/01/2011 9:00 AM10/03/2011 1:00 PM2.5 days10/01/2011 9:00 AM10/03/2011 6:00 PM3.0 days
2011-10-18 17:07:04 補充：
以下是美式日期顯示, 如果你用英式以日行先的話請自行修正
mm-dd HH:MM (英式 : dd-mm HH:MM)
A1 column 輸入 10/01/2011 2 PM 應輸入為 10-1 14:00 (日期同時間中間留一空格)
B1 column 輸入 10/01/2011 6 PM 應輸入為 10-1 18:00
這樣應會成功.
記得格式設定時用自定 : dd/mm/yyyy h:mm AM/PM
2011-10-17 11:43 pm
"我已找到可計算日期之間的相差日數"
請說說你已找到的方法是什麼, 是否Excel方法?
如須Provide Excel formula, 必須說明原始資料的格式
2011-10-17 16:13:00 補充：
A1= 1 Oct 2011 A.M.
B1= 3 Oct 2011 A.M.
C1 Formula:
=SUMPRODUCT(REPLACE(A1:B1,SEARCH("??M",A1:B1),9,)*{-1,1})+SUMPRODUCT(ISNUMBER(FIND({"A","P"},A1:B1))*0.5)
答案是 2.5
希望幫倒你
2011-10-17 16:51:11 補充：
如A.M. 及 P.M. 並非Excel格式的寫法, 故公式較長,
建議不要加點, 以AM 或PM表示
A1= 1 Oct 2011 AM
B1= 3 Oct 2011 AM
C1 Formula:
=SUMPRODUCT(REPLACE(A1:B1,SEARCH("?M",A1:B1),,"0 ")*{-1,1})+0.5
請說說你已找到的方法是什麼, 是否Excel方法?
如須Provide Excel formula, 必須說明原始資料的格式
2011-10-17 16:13:00 補充：
A1= 1 Oct 2011 A.M.
B1= 3 Oct 2011 A.M.
C1 Formula:
=SUMPRODUCT(REPLACE(A1:B1,SEARCH("??M",A1:B1),9,)*{-1,1})+SUMPRODUCT(ISNUMBER(FIND({"A","P"},A1:B1))*0.5)
答案是 2.5
希望幫倒你
2011-10-17 16:51:11 補充：
如A.M. 及 P.M. 並非Excel格式的寫法, 故公式較長,
建議不要加點, 以AM 或PM表示
A1= 1 Oct 2011 AM
B1= 3 Oct 2011 AM
C1 Formula:
=SUMPRODUCT(REPLACE(A1:B1,SEARCH("?M",A1:B1),,"0 ")*{-1,1})+0.5
2011-10-17 3:29 am
No hourly leave can be taken? Basic measurement is counted per each of half day?
2011-10-17 3:25 am
點解你唔去download 一些HR 考勤及計薪軟件?
要在excel 寫好你的要求
你個forumla 都有返咁上下長度
2011-10-17 08:37:20 補充：
還有.. Excel 做唔到一d..
例如 10月1日假期,該日人工要2倍
又例如某君
10月1日返由9:00 a.m. - 10:00pm
你可能已經當1.5 days
咁佢10月2日又返了 9:00 - 5::00
又未必可以咁準地計呢度係 2.5 days
而不是你之前的問題, 2 days
要在excel 寫好你的要求
你個forumla 都有返咁上下長度
2011-10-17 08:37:20 補充：
還有.. Excel 做唔到一d..
例如 10月1日假期,該日人工要2倍
又例如某君
10月1日返由9:00 a.m. - 10:00pm
你可能已經當1.5 days
咁佢10月2日又返了 9:00 - 5::00
又未必可以咁準地計呢度係 2.5 days
而不是你之前的問題, 2 days
收錄日期: 2021-04-13 18:18:23
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20111016000051KK00477