Working Days And Hours Between Two Dates And Times Suppose you have a schedule start date and time and an end date and time. Simply subtracting the end-time from the start-time will return the total number of days and hours between the times. However, suppose you want to know how many work days and hours the project will actually take. For example, suppose a project starts on Monday, 25-October-99 at 13:00 and ends on Thursday, 28-Oct-99 at 15:00, and that your normal working hours are 9:00 to 17:00. In this case, there are 3 days and 2 hours between the start and end times (2 full days, Tuesday and Wednesday, plus 4 hours on Monday and 6 hours on Thursday, for 2 days and 10 hours, or, since there are 8 hours in a workday, 3 days and 2 hours). The following formula will return the number of working days and hours between two dates and times. It returns a string like "3 days 2 hours". =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT), Where the following names are used:
To return the total number of working hours, use the following formula: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))) This will return the number of working hours as a numeric value, which can be used in other calculations. For example, if there are 8 hours in a working day, and the duration is 3 days, 6 hours (from the first formula), the formula above will return 30 (3*8 + 6). Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula. You can download an example workbook with these formulas here. NOTE: These formulas will not work properly if the working day starts at a later time of day than it ends. For example, if your working day crosses midnight, the formulas will not work properly.
| |||||||||||||||||||
Computing someone's age from their birthday is simple, using the =DATEDIF function. Suppose that a person's birthday is in A1. The following will return their age in Years, Month, and Days: =DATEDIF(A1,NOW(),"y")&" Y, "& DATEDIF(A1,NOW(),"ym")& If you want to create a series of dates in a column, consisting of only weekdays (Monday through Fridays), enter your starting date in a cell (A4, in the example), and then enter the following formula in the cell below that cell. =IF(WEEKDAY(A4)=7,A4+2,IF(WEEKDAY(A4)=6,A4+3,A4+1))
|
| Days Of The Week And Month Next Day Of Week After A Date To return the date of the next day of week following a given date, use the formula =A1+(WEEKDAY(A1)>=C1)*7-WEEKDAY(A1)+C1 Where A1 is a date and C1 is the day-of-week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday). For example, if A1 contains the date 15-Jan-2002 (a Tuesday) and C1 contains the number 6 (indicating Friday), the formula will return 18-Jan-2002, a Friday.
To return the number of days in a month, use the following formula. Suppose cell A1 contains the date 15-Jan-1998. =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) This will return 31, the number of days in January, 1998.
You can use the following formula to get the date of the first day of the month. Suppose the =DATE(YEAR(A1),MONTH(A1),1) Last Day Of Month You can use the following formula to get the date of the last day of the month. Suppose the =DATE(YEAR(A1),MONTH(A1)+1,0) You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month. Suppose the date in cell A1 is 1-June-2002. =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),1,0,0,0,0,0,2) This will return 3-June-2002. You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month. Suppose the date in cell A1 is 6-Nov-2002. =DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5)) =MAX(ROW(INDIRECT(DATE(year,month,1)&":"&DATE(year,month+1,0)))*(WEEKDAY(ROW(INDIRECT(DATE(year,month,1)&":"&DATE(year,month+1,0))),2)<6)) Last Day Of Previous Month You can use the following formula to get the date of the last day of the previous month. Suppose the date in cell A1 is 15-Jan-1998. =DATE(YEAR(A1),MONTH(A1),0)
This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week. This formula will return will return the date of Nth day-of-week for a given year. For example, it will return 11-April-2001 for the 15th Wednesday of the year 2001. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,1,1+(Nth-(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+ Where Yr,Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week. If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula: This formula assumes the following: You cannot use the =DATEDIF function of return the difference of of two dates in weeks, so you can use the following function return the number of weeks and days between two dates: =TRUNC((B1-A1)/7)&" Weeks "&MOD(B1-A1,7)&" Days"
If you need to know the day-of-week for a specific date, the =WEEKDAY function gives you a number between 1 and 7 indicating the day, (1=Sunday, 2=Monday, ... , 7=Saturday). To convert this number to the actual name of the day ("Sunday", for example), use the =TEXT function: To determine if a date is a work day (Monday - Friday), create a name called WorkDays which refers to Then following function will return TRUE or FALSE, indicating whether the date in A1 is a work day: To return the quarter of the year, use the formula =ROUNDUP(MONTH(A1)/3,0) .
If you need to know what week of the year at date falls in, use the following formula: =TRUNC(((A1-DATE(YEAR(A1),1,1))/7))+1+ This will return a number between 1 and 53, indicating the week of the year for the date in A1.
Last Weekday Of Month
|
No comments:
Post a Comment