Date functions
Date functions
Excel can give you the current date, based on your computer's regional settings. You can also add and subtract Dates.
Excel can give you the current date, based on your computer's regional settings. You
Check out the TODAY function, which gives you Today's date. These are live, or volatile functions, so when you open your wor
can also add and subtract Dates.
Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you how many days away it is b
Add Dates - Let's say you want to know what date a bill is due, or when you need to return a library book. You can add days to
GOOD TO KNOW
IMPORTANT
1 Excel keeps dates and times based on the number of days starting from January 1, 1900. Times are kept in fra
DETAIL
Time functions
In cellcan
Excel D28,
give you the current time, based on your computer's regional settings. You can also add and subtract times. For insta
Add up hours2
enter =NOW(),
between times
If this formula could talk, it would say, "Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Ti
Previous 3
Next
More information on the web
All about the TODAY function
All about the NOW function
All about the DATE function
Free Excel training online
Time functions
Excel can give you the current time, based on your computer's regional settings. You
can also add and subtract times. For instance, you might need to keep track of how
many hours an employee worked each week, and calculate their pay and overtime.
3
Date
Time
More information on the web
Date functions
Excel can give you the current date, based on your computer's regional settings. You
ck out the TODAY function, which gives you Today's date. These are live, or volatile functions, so when you open your workbook tomorrow
can also add and subtract Dates.
tract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you how many days away it is by using =D7-D6
Date
1 Check out the TODAY function, which gives you Today's date. These are live, or Today's date:
volatile functions, so when you open your workbook tomorrow, it will have
tomorrow's date. Enter =TODAY() in cell D6. Your birthday:
Days until your birthd
el can give you the current time, based on your computer's regional settings. You can also add and subtract times. For instance, you might
2 Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and Grace period days:
watch Excel tell you how many days away it is by using =D7-D6 in cell D8.
Bill due on:
is formula could talk, it would say, "Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Times, then multi
3 Add Dates - Let's say you want to know what date a bill is due, or when you
need to return a library book. You can add days to a date to find out. In cell D10,
enter a random number of days. In cell D11, we added =D6+D10 to calculate the
due date from today.
Time functions
Excel can give you the current time, based on your computer's regional settings. You
can also add and subtract times. For instance, you might need to keep track of how
many hours an employee worked each week, and calculate their pay and overtime.
1 In cell D28, enter =NOW(), which will give the current time, and will update Time
each time Excel calculates. If you need to change the Time format, you can Current Time:
go to Ctrl+1 > Number > Time > Select the format you want.
2 Add up hours between times - In cell D36 we've entered =((D35-D32)- Daily H
(D34-D33))*24, which calculates someone's start and end times, then Time In:
subracts the time they took for lunch. The *24 at the end of the formula Lunch Out:
converts the fractional portion of the day that Excel sees into hours. You'll Lunch In:
need to format the cell as a Number though. To do that, go to Home > Time Out:
Format > Cells (Ctrl+1) > Number > Number > 2 decimals. Total Hours:
3 If this formula could talk, it would say, "Take the Time Out and subtract it
from the Time In, then subtract the Lunch Out/In Times, then multiply those
by 24 to convert Excel's fractional time to hours", or =((Time In - Time
Out)-(Lunch In - Lunch Out))*24.
*24 to convert Excel's
Out)-(Lunch In - Lunch Out))*24.
*24 to convert Excel's
fraction of a day to hours
Time Out Lunch Out
Static D
Date:
=((D35-D32)-(D34-D33))*24 Time:
Time In Lunch In
GOOD TO KNOW
You can use keyboar
Times that won't con
The inner parentheses () make sure Excel calculates those
parts of the formula by themselves. The outer parentheses make
sure Excel multiplies the final inner result by 24.
Previous Next
More information on the web
All about the TODAY function
All about the NOW function
All about the DATE function
Free Excel training online
u open your workbook tomorrow, it will have tomorrow's date. Enter =TODAY() in cell D6.
y days away it is by using =D7-D6 in cell D8.
Date functions
Today's date:
Your birthday:
Days until your birthday: IMPORTANT DETAIL
ct times. For instance, you might need to keep track of how many hours anIfemployee
you don't worked
want Excel
eachtoweek,
displayanda negative
calculatenumber,
their pay and over
Grace period days: because you haven't entered your birthday yet, you can
Bill due on: use an IF function like this: =IF(D7="","",D7-D6), which
says, "IF D7 equals nothing, then show nothing,
e Lunch Out/In Times, then multiply those by 24 to convert Excel's fractional time to show
otherwise hours",
D7orminus
=((Time
D6".In - Time Out)-(Lunch In - Lunch O
GOOD TO KNOW
Excel keeps dates and times based on the number of days
starting from January 1, 1900. Times are kept in fractional
portions of a day based on minutes. So 01/01/2017 12:30
PM is actually stored as 42736.5208. If the Time or Date
show up as numbers like that, then you can press Ctrl+1 >
Number > select a Date or Time format.
Time functions
Current Time:
Daily Hours Worked
Time In: 8:00 AM
Lunch Out: 12:00 PM
Lunch In: 1:00 PM
Time Out: 5:00 PM
Total Hours: 8
Static Date & Time
Date: 09/27/17
Time: 8:52 AM
GOOD TO KNOW
You can use keyboard shortcuts to enter Dates and
Times that won't continuously change:
Date - Ctrl+;
Time - Ctrl+Shift+:
tive number,
ulate their pay and overtime.
day yet, you can
, which
Out)-(Lunch In - Lunch Out))*24.