Excel Tips and Tricks : Date & Time

Calculate the Difference between two set of time.

A very much needed Trick to compute the difference between two set of timing:

The magic formula to use is =MOD([Departure]-[Arrival],1)

How to Calculate Over time and wages.

Create a basic time sheet as described above, add lunch breaks , if required,

Step 1: Data Entry

In Cell J1 : type employee's hourly rate
In cell J2: type employee's overtime rate.
Change the Total Hours column to read: Regular Hours (Column F)
Add a new column called: Overtime Hours (Column G)
To simplify, you might want to remove the "Total Pay" Column
Format Columns F & G to "Number" with 2 Decimal places

Step 2: Calculate Hours worked
In Cell F2, type:     =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)
In cell G2, type        =IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)

Step 3: Drag down and total
In cell F10, sum regular hours, =SUM(F2:F8)
In cell G10, sum overtime hours =SUM(G2:G8)

Step 4: Calculate Pay:

In cell F11, type: =SUM(F10*$J$1)
In cell F12, type =SUM(G10*$J$2)
Sum F11 and F12 for total pay.  Type  =SUM(F11+G11)

CONVERT HOURS AND MINUTES INTO MINUTES

Convert Hours to Minutes in Excel. Here is how:

EXCEL 97-2003

Type your hours and minutes in an hh:mm format 

Click on "Format Cells"

On the Number Tab, under "custom", type [m] 

EXCEL 2007/2010/2013

Type your hours and minutes in an hh:mm format 

In the Excel Ribbon, Click on the Arrow Down near "Custom"

Click on "More Number Formats"

On the Number Tab, under "custom", type [m] 

Example:

Type 5:20

Change format to Custom and type [m] to see 320.

Question: How do I convert conventional hours into a decimal number?

Short Answer:

To convert hours using a calculator: 

Divide the minutes by 60. 

  

To convert hours using Excel: 

Change the cell format to "Number" with 2 decimal places and multiply it by 24

Example: My worker worked for 4hr 23 min at a rate of 13.50. How much do I pay him?

Step 1:       Divide:         23 / 60 = 0.38 

Step 2:       Add:             4 + 0.38 = 4.38

To pay your worker, multiply 4.38 by 13.50

USING EXCEL: 

If you like to use excel:

Step 1            In cell A1,                 Enter the time as hh:mm 

Step 2            In Cell B1, type         =(A1)*24 

Step 3            Change the format of Cell B to "Number" with 2 decimal places.

Example:

Step 1        In cell A1        Type 4:23  

Step 2        In cell B1       Type =(A1)*24 

Step 3        Change the format of cell B to "Number" with 2 decimal places.  You will now see 4.38

To pay your worker,  enter in cell C1 =(b1*13.50)

Make Time Count by using Formula

Imagine that you are using Excel to help you fill out a timecard by adding up the hours you work each day. To add the hours for Monday, you would type this formula in cell B4:

=(B3-B2)*24

As you type, the equal sign (=) tells Excel that this is a formula. B3 and B2 are thecell references that refer to the values in those cells. The minus sign operator tells Excel to subtract one value from the other.

Operations within parentheses are calculated first, so Excel subtracts first. Then the asterisk operator tells 

Excel to multiply the result of B3-B2 by 24, to convert that result from Excel's decimal time system to a 24-hour system. The result of the formula is 9 hours.

Computing Age

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")& 

" M, " & DATEDIF(A1,NOW(),"md") & " D".

Creating A Series Of Workdays

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))

Then use Fill Down to fill out your entire series of dates.

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.  

Note that if the day of week in C1 is same day of week as the date in A1, the formula returns a date 1 week later than A1.  For example, if A1 is 15-Jan-2002 (Tuesday) and C1 contains 3 (indicating Tuesday), the formula returns the date 22-Jan-2002, a Tuesday.  

If you want the formula to return the same date, use the formula

=A1+(WEEKDAY(A1)>C1)*7-WEEKDAY(A1)+C1.

Days In Month

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.

First Day Of Month

You can use the following formula to get the date of the first day of the month.   Suppose the 

date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1),1)

This will return 1-Jan-1998.

Last Day Of Month

You can use the following formula to get the date of the last day of the month.   Suppose the 

date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1)+1,0)

This will return 31-Jan-1998.

First Weekday Day Of Month

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.

Last Weekday Day Of Month

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))

This will return 29-Nov-2002.  Another formula, given a month and year, is the following array formula:

=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 will return 31-December-1997.

Nth Day Of Week For A Month And Year

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)+

(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth,  and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

Nth Day Of Week For A Year

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)+

Dow-WEEKDAY(DATE(Yr,1,1))

Where Yr,Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

Number Of Mondays In Period

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: 

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following: 

A2 contains the beginning date of the interval

B2 contains the ending date of the interval

C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

Week Differences

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"

What Day Is It?

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: 

=TEXT(A1, "ddd") returns the three character abbreviation for the day, e.g., "Mon". 

=TEXT(A1,"dddd") returns the full name of the day, e.g., "Monday".

To determine if a date is a work day (Monday - Friday), create a name called WorkDays which refers to 

={2;3;4;5;6}

Then following function will return TRUE or FALSE, indicating whether the date in A1 is a work day:

=IF(ISERROR(VLOOKUP(WEEKDAY(A1),WorkDays,1,0)),FALSE,TRUE)

What Quarter Is It?

To return the quarter of the year, use the formula 

=ROUNDUP(MONTH(A1)/3,0) .

What Week Is It?

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+

IF(WEEKDAY(DATE(YEAR(A1),1,1))>WEEKDAY(A1),1,0)

This will return a number between 1 and 53, indicating the week of the year for the date in A1.

How can I calculate the date that a project ends, this date falls after a certain amount of years, months and days?