Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
programming4us
Windows 7

Microsoft Excel 2010 : Building More Powerful Worksheets - Using Date & Time Functions

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
8/9/2011 4:33:24 PM

Calculating Dates

You can use different formulas to return a specific date. Here are some common examples you can use.

Calculate a Specific Day

You can use the DATE function to quickly calculate a specific day, such as New Year’s (January 1st), US Independence Day (July 4th), or Christmas (December 25th).

=DATE(A1,1,1)
=DATE(A1,7,4)
=DATE(A1,12,25)

Calculate a Changing Day

You can use the DATE and WEEKDAY function to calculate a holiday that changes each year, such as Thanksgiving, which is celebrated on the fourth Thursday in November.

=DATE(A1,11,1)+IF(5<WEEKDAY (DATE(A1,11,1)),
7-WEEKDAY(DATE(A1,11,1))+5,
5-WEEKDAY(DATE(A1,11,1)))+((4-1)*7)

Calculate the Day of the Year

You can calculate the day of the year for a specific date in the A1 cell. This function returns an integer between 1 and 365.

=A1-DATE(YEAR(A1),1,0)

Calculate the Day of the Week

You can use the WEEKDAY function to calculate the day of the week for a specific date in a cell. The function returns an integer between 1 and 7. This example returns 1 for Sunday, October 7, 2010.

=WEEKDAY(DATE(2010, 10,7))

Calculate a Person’s Age

You can use the DATEIF function to calculate the age of a person. The function returns an integer. For this example, A1 is birth date, A2 is the current date, and “y” indicates years (“md” indicates days and “ym” indicates months). If you loaded the Analysis ToolPak, you can also use the INT function.

=DATEIF(A1, A2,“y”) or INT(YEARFRAC(A1,A2))

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1 and each day counts up from there.

Working with Time

You can use different formulas to work with time. Here are some common examples you can use.

Display the Current Date and Time

You can use the NOW function to quickly display the current date and time. To display the current date and time, use the function:

=NOW()

Add Times

You can use the TIME function to quickly add different times together. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function:

=A1 + TIME(1, 35, 10)

Subtract Times

You can use the TIME function to subtract one time from another. To subtract 1 hour, 35 minutes, 10 seconds from a time in A1, use the function:

=A1 - TIME(1, 35, 10)

Rounding Times

You can use the TIME function along with the HOUR and MINUTE functions to round time up or down to the nearest time interval. To round to the previous interval (always going earlier or staying the same), use the function:

=TIME(HOUR(A1),FLOOR(MINUTE(A1),B1,0)

To round to the next interval (always going later or staying the same), use the function:

=TIME(HOUR(A1),CEILING(MINUTE(A1),B1,0)

The FLOOR and CEILING functions are Math & Trig functions for rounding numbers.

Other -----------------
- Microsoft PowerPoint 2010 : Editing Comments in a Presentation
- Microsoft PowerPoint 2010 : Adding Comments to a Presentation
- Microsoft Visio 2010 : Creating New Shape Data Fields
- Microsoft Visio 2010 : Refreshing All Data in Linked Diagrams
- Microsoft Visio 2010 : Linking Diagrams to External Data
- Forwarding Events (part 2) - How to Troubleshoot Event Forwarding & How to Configure Event Forwarding in Workgroup Environments
- Forwarding Events (part 1) - How to Configure Event Forwarding in AD DS Domains
- Visual Basic 2010 : Platform Invokes and Interoperability with the COM Architecture - P/Invokes and Unmanaged Code
- Microsoft Visio 2010 : Changing Shape Data Attributes
- Microsoft Visio 2010 : Viewing Shape Data Attributes
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer