Everything you need to know about how Excel handles date and time and all the tools you will need.
Read in conjunction with the accompanying Excel file, which you can download below.
Download the Files
Enter your email address below to download the comprehensive Excel workbook, cheat sheets and PDF.
Table of Contents
Regional Settings
When reading this post keep in mind that my regional settings format dates as dd/mm/yyyy and so the screenshots throughout this post are in this format.
However, if you open the accompanying Excel file you may see some dates have switched to match your regional settings, which may be different to mine e.g. mm/dd/yyyy.
Dates and times with a format that begins with an asterisk (*) automatically update based on your PC’s regional settings. You can see an example in the Format Cells dialog box below:
Ok, let's crack on.
Excel Date and Time 101
Excel stores dates and time as a number known as the date serial number, or date-time serial number.
When you look at a date in Excel it's actually a regular number that has been formatted to look like a date. If you change the cell format to 'General' you'll see the underlying date serial number.
The integer portion of the date serial number represents the day, and the decimal portion is the time. Dates start from 1st January 1900 i.e. 1/1/1900 has a date serial number of 1.
Caution! Excel dates after 28th February 1900 are actually one day out. Excel behaves as though the date 29th February 1900 existed, which it didn't.
For example, the date serial number for 31st Jan 1900 is 31. The date serial number for 1st Mar 1900 is 61, but it should be 60.
All dates after 28th Feb 1900 are 1 greater than they should be.
Microsoft intentionally included this bug in Excel so that it would remain compatible with the spreadsheet program that had the majority market share at the time; Lotus 1-2-3.
Lotus 1-2-3 was incorrectly programmed as though 1900 was a leap year. This isn't a problem as long as all the dates you are working with are 1st March 1900 or later.
Dates
Excel gives each date a numeric value starting at 1st January 1900.
1st January 1900 has a numeric value of 1, the 2nd January 1900 has a numeric value of 2 and so on.
These are called ‘date serial numbers’, and they enable us to do math calculations and use dates in formulas.
The Date Serial Number column displays the Date column values in their date serial number equivalent.
e.g. 1/1/2017 has a date serial number of 42736. i.e. 1st January 2017 is 42,736 days since 31st December 1899.
Tip: format the date serial number column as a Date and you'll see they look the same as the Date column values.
Time
Times also use a serial number format and are represented as decimal fractions.
Hours: since 24 hours = 1 day, we can infer that 24 hours has a time serial number of 1, which can be formatted as time to display 24:00 or 12:00 AM or 0:00.
Whereas 12 hours or the time 12:00 has a value of 0.50 because it is half of 24 hours or half of a day, and 1 hour is 0.041666' because it's 1/24 of a day.
Minutes: since 1 hour is 1/24 of a day, and 1 minute is 1/60 of an hour, we can also say that 1 minute is 1/1440 of a day, or its time serial number is 0.00069444'
Seconds: since a second is 1/60 of a minute, which is 1/60 of an hour, which is 1/24 of a day. We can also say one second is 1/86400 of a day or in time serial number form it's 0.0000115740740740741...
Date & Time Together
Now that we know how dates and times are stored we can put them together - ddddd.tttttt
For example, the date and time of 1st January 2012 10:00:00 AM has a date-time serial value of 40909.4166666667
40909 being the serial value representing the date 1st January 2012, and .4166666667 being the decimal value for the time 10:00 AM and 00 seconds.
More examples below.
Entering Dates & Times in Excel
Entering Dates
You can type in various configurations of a date and Excel will automatically recognise it as a date and upon pressing ENTER it will convert it to a date serial number and apply a date format on the cell.
For example, try typing (or even copy and paste) the following dates into an empty cell:
| 1-1-2009 |
| 1-1-09 |
| 1/1/2009 |
| 1/1/09 |
| 1-Jan-09 |
| 1-Jan 09 |
| 1-Jan-2009 |
| 1 Jan 09 |
| 1/1 |
You can see in the table above that entering numbers that look like dates and are separated by a forward slash or hyphen will be recognised as a date.
Even typing in a date with the month name gets converted to a date. And if you don't enter the year component, Excel will assume you mean the current year.
However, dates separated with a period like this 1.1.2009, or with spaces between numbers like this 01 01 2009, will end up as text, not a date. Gotta have some limits!
Tip: Dates that display ##### in a cell usually indicate that the column is simply not wide enough to display it.
However, if you make the cell really wide and it still displays ##### then this indicates that the date is a negative value and Excel can't display negative dates.
Entering Dates with Two Digit Years
When you enter a date with two digits for the year e.g. 1/1/09, Excel has to decide if you mean 2009 or 1909.
It goes by the rule that dates with years 29 or before, are treated as 20xx and dates with the year 30 or older are treated as 19xx. See examples below.
Tip: You can enter the day and month portions of a date and Excel will insert the year based on your computer's clock. Nice to know for data entry.
Entering Time
When you enter time you must follow a strict format of at least h:mm. i.e. the hour and minutes are separated by a colon with no spaces either side.
Entering the h:mm components will result in a time formatted in military time e.g. 2:00 PM is 14:00 in military time.
If you enter a time that includes a seconds component e.g. 3:15:40, Excel will automatically format the cell in h:mm:ss.
If you want the time to be formatted with AM/PM you can simply enter a space after the time and then type AM or PM, or apply the number format to the cell later. Here are some examples:
Entering Dates & Time Together
Now that we know how to enter dates and time separately we can put them together to enter a date and time in the same cell.
You can even enter time then date and Excel will fix the order for you.
You'll find that even if you enter AM/PM, Excel will convert it to military time by default. You can override this with a custom number format. More on that later.
Simple Date & Time Math
Now that we understand that Excel stores dates and time as serial numbers, you'll see how logical it is to perform math operations on these values.
We'll look at some simple examples here and tackle the more complex scenarios later when we look at Date and Time Functions.
Adding/Subtracting Days from Dates
Tip: you can also add/subtract the days directly in the formula e.g. =B10+10 or =B11-5 Although, it's better to place the values you're adjusting by in their own cell or a named range.
Subtracting Dates from one another
Tip: format the cell to General or Number to see the number of days between two dates.
Note: the ‘result’ is exclusive of the start day i.e. it assumes the start day is at the end of that day.
Adding Times to one another
The time being added is input as a time serial number. Notice there are no negative times in the table below.
Remember we can't display negative times. Instead we need to use the math operator to tell Excel to subtract time. See examples below.
Note: Times that roll over to the next day result in a time-date serial number >= 1.
Cell E28 actually contains a time-serial number of 1.08333', but since the cell is formatted to display time formatted as h:mm:ss, only the time portion is visible.
If you want to show the cumulative time (like cell E29) then you need to surround the 'h' part of the time format in square brackets like so: [h]:mm:ss
Subtracting Time from Times
Notice the last result in the table below shows ######, this is because it results in a negative time and Excel can't display that, but notice it can return a negative time serial number. More on how to solve this later.
Subtracting Times from one another
Again, here the last result shows ###### because it results in a negative time.
Excel Date and Time Shortcuts
Excel Keyboard Shortcuts for PC and Mac
Work faster and more efficiently with our list of Excel shortcuts.
'Good to Know' Stuff about Excel Date and Time
- Dates prior to 1st January 1900 are not recognised in Excel.
- A negative date will display in the cell as #######
- Times stored without a date effectively inherit the date 0 Jan 1900 i.e. the month is Jan and the year 1900 and the day is zero. Remember, there are no dates prior to 1/1/1900 from Excel's perspective. This means that times stored without a date e.g. 0.50 for 12:00 PM is the equivalent of 0 Jan 1900 12:00 PM.
This is important because if you try to take 14 hours from 12 hours (without a date) you'll get the dreaded ###### display in the cell, because negative dates and times cannot be displayed. We'll cover workarounds for this later, but for now keep in mind that math on dates and time that result in negative date-time serial numbers cannot be formatted as a date.
Date Modes
Excel actually has two date modes. The other mode is called 1904 Date System and is used for compatibility with Excel 2008 for Mac and earlier Mac versions. You can change the date system in the Advanced Options.
In the 1904 date system dates are calculated using 1st January 1904 as the starting point.
The difference between the two date systems is 1,462 days. This means that the serial number of a date in the 1900 date system is always 1,462 days greater than the serial number of the same date in the 1904 date system.
1,462 days is equal to four years and one day (including one leap day).
Caution; the date setting you choose applies to all dates within the workbook. You can't mix and match modes and you shouldn't reference workbooks that use a different date system in formulas.
Bottom line; don't use the 1904 date system unless absolutely necessary! Click here for more on date systems in Excel.
- Excel applies date number formats based on your system region settings. For example, my system is set to display dates in dd/mm/yyyy format, but if you're in the U.S. your system is likely to format them as mm/dd/yyyy.
Excel will automatically convert the format of date serial numbers to suit your system settings as long as it's one of the default date formats and not a custom number format.
More Excel Date and Time Tips
This post is just the beginning, the next steps in mastering Excel Date and Time are below:
- Every Excel Date and Time Function explained
- Formatting Date and Time in Excel
- Common Date and Time Calculations
Tip: Avoid waiting, download the workbook and get the above topics now.
Enter your email address below to download the comprehensive Excel workbook and PDF.
Pro Excel Chart Formatting
Sébastien
Thanks Mynda, useful and superclear as ever.
I would suggest an addition on the international settings section. You can force a locale on the date or time by adding a prefix to the date or time format. As an example, “[$-fr-CA]d mmmm yyyy” would apply the french canadian date format, even with an english setup.
The letters used for the custom format for date and time could also be different depending on your installation. In French, it would “j” for the day instead of “d” and “a” for the year instead of “y”.
Mynda Treacy
Thanks, Sebastien! Great addition, thanks. You might like this post where I used that technique to switch date formats using data validation and those language IDs.
Rajat Choudhary
Thanks for telling this information about this course
Mynda Treacy
Our pleasure!
ExplicitChimp
Hi Mynda,
Thanks for the information, it is most helpful. There is a typo in your text, 1 hour is 0.041666′ not 0.41666′ as stated in your text in this paragraph:
“Whereas 12 hours or the time 12:00 has a value of 0.50 because it is half of 24 hours or half of a day, and 1 hour is 0.41666′ because it’s 1/24 of a day.”
Kind regards,
EC
Philip Treacy
Thanks EC, I fixed the typo.
regards
Phil
AYAL TELEM
Hi Mynda,
Thank you for a much needed info on these tricky values.
I have unrelated question, though: how did you add the “Note” on sheet “Functions” cell D4?
It’s not a Note or Comment, and I found nothing View Code.
Regards.
Mynda Treacy
I used the Data Validation Input Message field for the note in cell D4
AYAL TELEM
Thank you very much – small but useful tip.
Regards.
Bob Reynolds
Dear Mynda:
Is there a way to get Excel to recognize tenths and hundredths of seconds in the =Time function? For example when a runner has lap times of 59.23 =TIME(0,0,59.23) + 61.01 =TIME(0,1,01.01) + 62.70 + 59.08 Excel will sum the four laps as 4:01.00 minutes and not 4:02.02.
Thanks.
Mynda Treacy
Hi Bob,
The TIME function cannot handle hundredths of a second. Instead, enter the time directly in the cell as: 0:0:59.23 and 0:0:61.01 and then add the two cells together. Use a custom number format to display the hundredths like so: h:mm:ss.00
Mynda
Martin Nolan
Hi Mynda,
In adding/subtracting columns of times displayed as [h]:mm, it appears that Excel always rounds the decimal time down to the previous minute. Thus, the total will always have some accumulated error. The times must be displayed as [h]:mm:ss in order to display a correct total. Rounding down also occurs when converting a decimal time to [h]:mm using the TEXT function.
Mynda Treacy
Good to know, Martin. Thanks for sharing.
Gabriel
Hi Mynda
I was just reading your blog post and I would like to add to this. You state that negative time cannot be calculated in Excel, but if you change one option then it will be done. You should set the default date system to use the Lotus123 compatible format where 1 = 1/1/1904. Then times can be negative – must be programmers logic, I guess.
kind regards
Gabriel
Mynda Treacy
Ah, good to know, Gabriel!
AYAL TELEM
Might it be that the programmer wanted to allow some convertion/compatability to the 1900 format?
Alan Sansome
We store data in our SQLserver database and export out to Excel for public and internal use. Clearly history didnt start on 1 Jan 1900 we have historical events in our database that when exported clearly has no date excel recognises because it occurred in the 1890’s. We don’t seem to have any work around to this, the date comes into excel from Sqlserver as -xxx days (-4777) prior to 1 Jan 1900. Any thoughts?
Mynda Treacy
Hi Alan,
There are some workarounds, like those explained here.
Mynda
sandeep kothari
Very useful post, Mynda! Thanks a ton.
Mynda Treacy
Great to hear, Sandeep!
Waleed Ahmed
I am stuck in a problem there are total 4 cells i need to enter end time in one cell and it should appear automatically in one of the three cells
End Time Before 7 AM After 7 AM After 8:30 AM
if enter time in END TIME it should appear in one of these cell according to its time
Philip Treacy
Hi Waleed,
Presumably for a time after 8:30 am you only want 1 of the cells ‘After 7am’ and ‘After 8.30am’ to contain data?
See this file for a solution https://d13ot9o61jdzpp.cloudfront.net/files/time.xlsx
regards
Phil
Jose
Hello,
I am trying to calculate a formula. i want to know what time i would need to clock out for lunch if i punch in at a certain time and need to clock out by the 5th hour.
Example,
If i come in at 6:45am, what time would i have to clock out for lunch to stay with in the 5th hour mark.
I would like to punch in any time frame and get the time in another cell that i need to clock out by.
Mynda Treacy
Hi Jose,
I’m not sure I understand what you mean by the 5th hour, but perhaps this formula is what you’re after:
=A1+TIME(4,59,0)
Where your punch in time is in cell A1.
Mynda
benito
Hi Mynda,
Thanks for this.
Hoping I could get your help on how I could get excel to tell me the duration between 2 dates ie.
Thu, 4th Nov 2021 3:52pm
and
Tue, 2nd Nov 2021 10:56am
Ideally the output would be in minutes
Catalin Bombea
Hi Benito,
If you deduct the dates, you will get: 2.2055556, in decimal system this means 2.2 days.
Multiply the result with 24 and you’ll get the number of hours = 52.9333.
Multiply the result by 60 (minutes) and you’ll get minutes = 3176
Formula is simple:
=(A1-A3)*24*60
Charissa
Hello, I imported data from an Aprima EMR software report to an excel sheet. The data originally imported as text in this form ” 2..846″ . I then changed the format from text to time (not the custom option). The time display is “8:18:14 PM”. The problem is when I click in in the cell on the work sheet the large box below the ribbon with all the tabs displays “1/2/1900 8:18:14 PM.” I am trying to figure out how to convert “1/2/1900 8:18:14 PM” into a total time in only minutes format. Is this possible? If so please tell me how. Thank you
Mynda Treacy
Hi Charissa,
You can use this formula to remove the date component of the time where your time is in cell A1: =A1-INT(A1)
Mynda
Roger Fraser
Thank you for solving a time issue that I had spwnt hours trying to fix. MUCH appreciated!!
Mynda Treacy
So glad we could help, Roger 🙂
Russ Gloster
The way Microsoft deals with dates is one of the dumber things Microsoft has come up with. Just like workarounds resulting from using MS-DOS still.
I worked for a life insurance company in the 1970s writing programs in assembler code and the programmers had already worked out how to deal with dates prior to 1st January 1900 and the fact that 1900 was not a leap year. At the time some of our customers were born before 1900 so it was important for life insurance applications.
A simple solution is to introduce negative serial number for dates, so that dates prior to 1st Jan 1900 would be negative and all the formatting and calculations could take this into account.
The problem is similar to saying “Well we write everything on parchment so we can’t change the way we do things so let’s stay in the past and ignore Gutenberg!!!”
olga
Hello, I need help with my formula, so I am keeping track of projects and individual dates and time elapsed while completing the project.
Example: start 4/19/21, 8:30 am , end 4/24/21, 9:53 am, 8 hour schedule and some Saturdays included. Now the formula I use works but how can I include Saturdays? here is formula I use.
=(NETWORKDAYS(START,END)-1)*(“15:45”-“7:00″)+IF(NETWORKDAYS(END,END),MEDIAN(MOD(E,1),”15:45″,”7:00″)-MEDIAN(NETWORKDAYS(START,START)*MOD(START,1),”15:45″,”7:00”)
WORKDAY IS 7AM- 3:45 PM.
All help is greatly appreciated. thank you
Mynda Treacy
Hi Olga,
Use the NETWORKDAYS.INTL function as this allows you to specify that only Sunday is a day off.
Mynda
Neil Sheldon
Great Info! … Thanks
Mynda Treacy
Our pleasure, Neil!
LouAnne
Is there a way to determine the date an entry was made in a cell in excel? If so, how is this done?
Philip Treacy
Hi LouAnne,
No not really. Unless you implement some VBA solution for every change made in the workbook but even that is not reliable.
Regards
Phil
Mehrane
Hello,
I cannot find my case here.
In my data bank, the date format entered in the cell is 19780815 (mean year=1978 and the month= 08 and day=15). How can I change it to date format?
Catalin Bombea
Split the text:
=Date(Left(A1,4),MID(A1,5,2),Right(A1,2))
Dhana Lakshmi
Please can you type and show how date and time to entered at once to get serial number
Philip Treacy
Hi Dhana,
Entering this into a cell will give you this date and time
19/06/2018 4:03:00 PM
But if you are not seeing both date and time, your cell probably isn’t formatted correctly.
You need to select the cell then open format cells (CTRL + 1) and on the Number tab click no Custom.
Under the Type you need to have d/mm/yyyy h:mm AM/PM and click OK.
If this does not solve your issue please start a topic on the forum and attach your file.
Regards
Phil
Jayden
Is there a way to make it so your calculations change if it becomes a new year(e.g. i have =C11*0.1 and i want the 11 to change to a 12 on a certain date every year) how do i do that?
Mynda Treacy
Hi Jayden, it’s a bit tricky to follow what you mean. Please post your question and an example Excel file on our forum where we can help you with a solution. Mynda
Camilo Malagon
Hello
I put a date on A1, then a number on A2 (for days), and in A3 I enter =A1+A2
But I get “0-Jan”
What can be the problem?
10-Feb-20 5 0-Jan
I had not find a solution neither a blog that report the same problem.
Thank you
Mynda Treacy
Hi Camilo,
I suspect the date in cell A1 isn’t a proper date serial number. If you press the keys CTRL and ` (the back tick ` shares the same key as the tilde ~) at the same time, the date in cell A1 (10-Feb-20) should change to the number 43871. If it doesn’t then your date isn’t in the right format for Excel to perform math on. You need to fix the date formats using one of these techniques:
– Text to columns to fix date formats
– 6 Ways to Fix Dates Formatted as Text in Excel
Mynda
Steve
Hi – I’ve merged a Date and Time into one cell by using A8=TEXT(B8,”mm/dd/yyyy”)&TEXT(C8,”hh:mm:ss”) and then I have been trying to add 10 minutes to the datetime by the formula =a8+(10/1440), however it just returns #VALUE!. Appreciate your assistance to resolve.
Philip Treacy
Hi Steve,
You’ve converted the value in A8 to text so you can’t do maths with text.
If you have a date in B8 and a time in C8, just add them together.
Why not add the 10 minutes to C8 before doing anything else to it?
Regards
Phil
zdanman
Hello, I’m trying to figure out an excel formula showing a date yyyy-mm-dd HH:MM:SS is between work hours of 8am and 5pm, resulting in “working hours”, “off hours”?
Philip Treacy
Hi Daniel,
You need to extract the time part of the date/time in order to compare it against your 8am and 5pm working day. The MOD() function will do this. The full formula is
Regards
Phil
M
Hi,
As I type dates in Excel, it keeps reading it as Day and Year instead of Day and Month.
For example, I encoded “Aug 30” excel is storing it as Aug 1, 1930.
How can I fix this?
Thanks.
Mynda Treacy
It sounds like your PC settings are set to a region that uses dd/mm/yyyy date formats. If you enter “30 Aug” into a cell it will convert it to 30 Aug 2019. If your dates should be formatted mm/dd/yyyy then you need to change your region settings. Then when you enter Aug 30, it will return Aug 30, 2019.
Mynda
Chirag
Hi, When I am changing the format from hh:mm:ss to hh, the display value is changing but the underlying value id remaining the same i.e. hh:mm:ss. It is creating a problem when I am pivotting it. Can i get any help on this.
Catalin Bombea
What problem is created when pivoting?
Chirag
as the original value is remaining as hh:mm:ss the total number of values is to high to put in column section. If the original value comes in hh then the total number of values will only be 24.
Catalin Bombea
You will have to add a new column in your data, to extract the hour only, and use this column in your pivot.
=HOUR(DateTimeColumn)*24 (multiply to 24 to convert to decimal system, otherwise 12 hours will be displayed as 0.5 in time system)
Chirag
It is not working. For this value in date time column (11-05-2019 10:15:00
) I tried.
Catalin Bombea
You will have to use our forum to upload your file, I guess you may have the dates as text, not a formatted number. Please read more carefully the differences between a text and a date value.
Here is our forum, you can upload a sample file (create a new topic after sign-up)
Duminda
Can anyone help with the desired output:-
Input Data Would be in Format of – 5 days 8:25:28
Desired Output Should be in Hours – 128.416
Mynda Treacy
Hi Duminda,
Multiply the duration of 128:25:28 by 24 to convert to decimals. i.e. 128 hours is 5 days 8 hours.
Mynda
Ron Vissing
I am populating a webform from Excel and all works great except for the date of birth field. The website has it HTML coded as Input Value Type= Text mm/dd/yyyy. No matter how I format the DOB cell in Excel, the webform populates it as “Thu May 16 1946 00:00:00 GMT-0400 (Eastern Daylight Time)”. Any thoughts on how to format my Excel DOB field to input it as mm/dd/yyyy?
I’ve looked at your courses and pricing and will definitely be back as I’m entering this as a career
Mynda Treacy
Hi Ron,
Unfortunately the webform is returning text that can’t simply be formatted as a date because Excel doesn’t recognise “(Eastern Daylight Time)” as date information. You could add a helper formula in a cell beside the text date returned by the webform that extracts the date information from the cell. This tutorial should set you on the right path, but if you get stuck, please post your question on our Excel forum where we can help you further.
Mynda
Steven Owens
Hi, I hope you can help.
I have a project where I need to take one date time combination from another one, but only need the time as the answer. In other words [start date/time – finish date/time]= hours/minutes
Any help on a formula that can do this would be much appreciated.
Thanks
Steve
Mynda Treacy
Hi Steve,
Apply a custom number format:
[h]:mm
Mynda
Luuk
Dear Mynda,
Do you know how to make an scatter- or line graph with different time intervals on the x-as? Because my current data contains different intervals and I would like to get a more realistic representation of the data.
Kind regards,
Luuk
Mynda Treacy
Hi Luuk,
If your axis values are proper dates it should automatically detect them as dates and plot the axis with evenly spaced time intervals, even if your source data isn’t evenly spaced. If you’re still stuck, please post your question on our Excel forum where you can upload a sample Excel file and we can help you further.
Mynda
Michael
Hello Mynda,
I was hoping you could help me out with a similar issue.
My regional settings are set to US, however Excel will only accept date *entry* in “dd/mm” format.
For example Entering “4/28” yields a date for April 1, 2028, not April 28th of this year, which is what I would intend.
I can get my head around typing the data in using this method, however when I import some data, this requires an extra step, which I would prefer to avoid.
Do you know of a change in settings which does not just control the display, but specifically the input method for dates?
Thanks & Regards,
Mike
Mynda Treacy
Hi Michael,
It sounds like there is a region setting somewhere (either Excel or Windows) that is interfering with the date format. When you enter 4/28 it should return 4/28/19. I’m not sure what process you’re following to import data e.g. copy and paste, get with Power Query etc., but either way, it should handle dates as per your region settings.
Mynda
Anil
How do i calculate coming date and time after a certain time. Ex: A1 cell contains a adte and time (MM/DD/YY HH:MM:SS PM/AM) A2 cell contains Durations (110:30 hrs), i have to get date and time after 110:30hrs in A3 cell, what is the formula for cell A3?
Catalin Bombea
Hi Anil,
A simple =A1+A2 should be enough in cell A3.
Tessa
Hello,
I currently have a report that shows the date and a military time stamp altogether. EX: 2019-03-28T21:18:19+00:00
Would anyone know how to convert it to just a date?
Catalin Bombea
Hi Tessa,
Date should be:
=DATEVALUE(LEFT(A1,FIND(“T”,A1)-1))
Time:
=TIMEVALUE(RIGHT(LEFT(A1,FIND(“+”,A1)-1),8))
For date and time:
DATEVALUE(LEFT(A1,FIND(“T”,A1)-1))+TIMEVALUE(RIGHT(LEFT(A1,FIND(“+”,A1)-1),8))
Nicholas
Evening all,
I have an equation in excel that says ” 43.33 ” i want this to say 00:43:33 in a time format so i can add it too another time format cell to give me an exact finishing time for one of my programs
anybody have any idea
Mynda Treacy
Hi Nicolas,
Assuming 43.33 represents 43 hours and .33 of the remaining hour, as opposed to 43 hours and 33 minutes. If so, you can convert it to hh:mm by dividing 43.33/(60*24)
Alternatively, you’d have to separate it into its time components. e.g. assuming 43.33 is in cell A1:
This requires the structure of the value in the cell to always be hh.mm as opposed to h.mm or hh.m.
Mynda
David Dresser
Your site is very helpful!
I’m trying write formula to change answer based on the time of day. Example I would like to show a number if it is before a time of day (today) and blank after the rest of the day.
Thank You
David
Catalin Bombea
Hi David,
Use =(NOW()-TODAY()) to calculate the time of day. To compare with a specific hour, use =(NOW()-TODAY())<12/24, where 12/24 obviously means the middle of the day. Change to 8/24 for 8:00AM, or 8.5/24 for 8:30AM for example.
Colin Nicholas
Greetings
I cannot get the formula below to work properly
=IF(“today”>$E$3,$C20,$B20)
can you assist please
thank you
Mynda Treacy
Hi Colin,
I think you mean:
Mynda
Stefan Johansson
Thanks, Mynda, for a superb article! It really took out all mystery from using date and time in calculations.
Since you encouraged us to spread word, I posted it on my blog, for my readers to learn about it, as well. The URL is:
https://medieborgaren.se/2017/11/17/2017-11-17-friday/
Mynda Treacy
Thanks, Stefan! Glad you enjoyed it and thanks for sharing.
Karim Sumar
Good tips. Couldn’t get the shortcut
CNTRL+Shift +# to work
Perhaps it’s my keyboard!
Mynda Treacy
Thanks, Karim. Shame the CTRL+SHIFT+# shortcut doesn’t work for you.
julian
Instead of inserting a comment in a cell, using input message of data validation is a good option. I like it. Besides, what I’m saying is each of your articles including the companion like a textbook with most elaborate content I’ve ever seen. You are really awsome.
Mynda Treacy
Thanks, Julian! Glad you discovered something new and useful with the data validation ‘comment’.
Mickael Taieb
Mynda,
Thank you so much for your post. I learn a wealth of information from you and get better at Excel with every single one of your posts.
Which single or combination of formulas are best to use to calculate fiscal years?
Best regards,
Mickael
Mynda Treacy
Hi Michael,
Thanks for your kind words.
There’s no ‘date’ function for fiscal years i.e. periods that differ to the calendar year, but this post explains your options for converting dates to fiscal years.
Mynda
jim
On my uk keyboard (where there is a ~/# key next to the enter key) ctrl + # applies date format, ctrl + shift + # (ie ctrl + ~) applies general format
other keyboard layouts are available
Tip: I always show dates as d-mmm-yy, just to make sure nothing american sneaks through (but Access isn’t too keen on this)
Another tip: I get a lot of CSV data where the dates are in dd.mm.yy format; simply select the column, replace “.” with “/” and proper dates appear. Times are more of a challenge, coming as hhmm (with many spaces and 12:mm am shown as just mm). Here I need to designate the column as text on loading (to stop Excel assuming too much), apply the formula =TIME(A1%,MOD(A1,100),) and copy back the values
PowerQuery could help but the layout is annoyingly inconsistent
Slightly off-topic tip: some of our material codes are like 1234E56, which Excel helpfully interprets as 1.234×10^59 – unless it’s an actual numeric value, always download as text!
sorry to over-complicate your simplified introduction
jim
Mynda Treacy
Thanks for sharing your tips, Jim. The more the merrier 🙂
Thomas H.
Hi Mynda,
thanks for sharing!
Hint: “However, dates separated with a period like this 1.1.2009, or with spaces between numbers like this 01 01 2009, will end up as text, not a date. Gotta have some limits!” – is not true for “Germany” set as region – then it will be recognised as date as well.
Mynda Treacy
Lucky you, Thomas. Here in Australia, Excel is more fussy 🙂 Thanks for sharing.
Badri
How to change month and date only in 29/12/2018 6:00 using formula if they are in the same cell
Catalin Bombea
If you want to add days, simply add the number of days needed to that value: =A1+30
If you want to add time, add it as a fraction of 24 hours: to add 3 hours: =A1+3/24