• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Date and Time

You are here: Home / Excel / Excel Date and Time
excel date and time
October 17, 2017 by Mynda Treacy

The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need.

It’s designed to be 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 and PDF.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

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:

Excel Date and Time regional settings

Ok, let's crack on.

Excel Date and Time 101

In a nutshell

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.

Excel Date and Time serial number example

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.

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 your dates are later than 1st March 1900.

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.

Excel Date serial number examples

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.41666' 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...

Excel Time serial number examples

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.

Excel Date and Time serial number examples

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:

Entering dates in Excel

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.

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!

#### date error

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.

entering dates with two digit years

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 time in excel

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.

entering time and date together in excel

You'll find that even if you enter AM/PM, that 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

simple date and time math in excel

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

subtract dates in excel

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.

add time in excel

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.

subtract time in excel

Subtracting Times from one another

Again, here the last result shows ###### because it results in a negative time.

subtract time from times in excel

Excel Date and Time Shortcuts

Time and date shortcut keys

'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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.

excel date and time

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:Power Query Get Files from a Folder
Next Post:Excel NETWORKDAYS.INTL Function

Reader Interactions

Comments

  1. Waleed Ahmed

    February 23, 2022 at 3:00 pm

    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

    Reply
    • Philip Treacy

      February 23, 2022 at 5:07 pm

      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

      Reply
  2. Jose

    February 23, 2022 at 12:29 am

    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.

    Reply
    • Mynda Treacy

      February 23, 2022 at 9:50 am

      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

      Reply
  3. benito

    February 18, 2022 at 6:04 am

    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

    Reply
    • Catalin Bombea

      February 18, 2022 at 3:11 pm

      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

      Reply
  4. Charissa

    February 8, 2022 at 10:36 am

    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

    Reply
    • Mynda Treacy

      February 8, 2022 at 2:52 pm

      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

      Reply
  5. Roger Fraser

    January 11, 2022 at 1:51 pm

    Thank you for solving a time issue that I had spwnt hours trying to fix. MUCH appreciated!!

    Reply
    • Mynda Treacy

      January 11, 2022 at 2:17 pm

      So glad we could help, Roger 🙂

      Reply
  6. Russ Gloster

    July 11, 2021 at 12:31 pm

    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!!!”

    Reply
  7. olga

    May 27, 2021 at 11:02 am

    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

    Reply
    • Mynda Treacy

      May 27, 2021 at 1:16 pm

      Hi Olga,

      Use the NETWORKDAYS.INTL function as this allows you to specify that only Sunday is a day off.

      Mynda

      Reply
  8. Neil Sheldon

    December 19, 2020 at 10:53 am

    Great Info! … Thanks

    Reply
    • Mynda Treacy

      December 19, 2020 at 1:19 pm

      Our pleasure, Neil!

      Reply
  9. LouAnne

    November 30, 2020 at 11:47 pm

    Is there a way to determine the date an entry was made in a cell in excel? If so, how is this done?

    Reply
    • Philip Treacy

      December 1, 2020 at 7:58 am

      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

      Reply
  10. Mehrane

    November 24, 2020 at 12:54 pm

    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?

    Reply
    • Catalin Bombea

      November 24, 2020 at 2:01 pm

      Split the text:
      =Date(Left(A1,4),MID(A1,5,2),Right(A1,2))

      Reply
  11. Dhana Lakshmi

    August 7, 2020 at 3:46 am

    Please can you type and show how date and time to entered at once to get serial number

    Reply
    • Philip Treacy

      August 7, 2020 at 9:17 am

      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

      Reply
  12. Jayden

    July 23, 2020 at 3:29 am

    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?

    Reply
    • Mynda Treacy

      July 23, 2020 at 9:19 am

      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

      Reply
  13. Camilo Malagon

    March 4, 2020 at 5:55 pm

    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

    Reply
    • Mynda Treacy

      March 4, 2020 at 7:08 pm

      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

      Reply
  14. Steve

    September 22, 2019 at 11:24 am

    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.

    Reply
    • Philip Treacy

      September 22, 2019 at 11:54 am

      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

      Reply
  15. zdanman

    September 20, 2019 at 4:15 am

    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”?

    Reply
    • Philip Treacy

      September 20, 2019 at 8:56 am

      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

      =IF(AND(MOD(A1,1)>=TIME(8,0,0),MOD(A1,1)<=TIME(17,0,0)),"Working Hours","Off Hours")
      

      Regards

      Phil

      Reply
  16. M

    September 13, 2019 at 11:39 am

    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.

    Reply
    • Mynda Treacy

      September 13, 2019 at 12:28 pm

      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

      Reply
  17. Chirag

    July 30, 2019 at 9:45 pm

    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.

    Reply
    • Catalin Bombea

      July 31, 2019 at 12:18 am

      What problem is created when pivoting?

      Reply
      • Chirag

        July 31, 2019 at 2:49 am

        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.

        Reply
        • Catalin Bombea

          July 31, 2019 at 3:10 am

          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)

          Reply
          • Chirag

            July 31, 2019 at 4:12 am

            It is not working. For this value in date time column (11-05-2019 10:15:00
            ) I tried.

          • Catalin Bombea

            July 31, 2019 at 12:37 pm

            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)

  18. Duminda

    July 24, 2019 at 3:11 pm

    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

    Reply
    • Mynda Treacy

      July 25, 2019 at 4:13 pm

      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

      Reply
  19. Ron Vissing

    July 17, 2019 at 9:15 am

    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

    Reply
    • Mynda Treacy

      July 17, 2019 at 9:54 am

      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

      Reply
  20. Steven Owens

    July 11, 2019 at 6:42 pm

    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

    Reply
    • Mynda Treacy

      July 13, 2019 at 7:16 am

      Hi Steve,

      Apply a custom number format:

      [h]:mm

      Mynda

      Reply
  21. Luuk

    June 15, 2019 at 1:38 am

    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

    Reply
    • Mynda Treacy

      June 16, 2019 at 11:17 am

      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

      Reply
  22. Michael

    June 4, 2019 at 7:25 am

    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

    Reply
    • Mynda Treacy

      June 4, 2019 at 8:52 pm

      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

      Reply
  23. Anil

    May 9, 2019 at 10:12 pm

    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?

    Reply
    • Catalin Bombea

      May 10, 2019 at 3:10 am

      Hi Anil,
      A simple =A1+A2 should be enough in cell A3.

      Reply
  24. Tessa

    April 10, 2019 at 12:59 am

    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?

    Reply
    • Catalin Bombea

      April 10, 2019 at 4:48 am

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

      Reply
  25. Nicholas

    March 5, 2019 at 5:25 am

    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

    Reply
    • Mynda Treacy

      March 5, 2019 at 11:32 am

      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:

      =TIME(,VALUE(LEFT(A1,2)),)+TIME(,,VALUE(RIGHT(A1,2)))

      This requires the structure of the value in the cell to always be hh.mm as opposed to h.mm or hh.m.

      Mynda

      Reply
  26. David Dresser

    November 24, 2018 at 1:37 pm

    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

    Reply
    • Catalin Bombea

      November 24, 2018 at 5:33 pm

      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.

      Reply
  27. Colin Nicholas

    December 30, 2017 at 2:12 pm

    Greetings
    I cannot get the formula below to work properly

    =IF(“today”>$E$3,$C20,$B20)

    can you assist please
    thank you

    Reply
    • Mynda Treacy

      December 30, 2017 at 2:59 pm

      Hi Colin,

      I think you mean:

      =IF(TODAY()>$E$3,$C20,$B20)

      Mynda

      Reply
  28. Stefan Johansson

    November 18, 2017 at 10:27 am

    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/

    Reply
    • Mynda Treacy

      November 18, 2017 at 11:25 am

      Thanks, Stefan! Glad you enjoyed it and thanks for sharing.

      Reply
  29. Karim Sumar

    October 25, 2017 at 9:59 pm

    Good tips. Couldn’t get the shortcut
    CNTRL+Shift +# to work
    Perhaps it’s my keyboard!

    Reply
    • Mynda Treacy

      October 26, 2017 at 9:16 am

      Thanks, Karim. Shame the CTRL+SHIFT+# shortcut doesn’t work for you.

      Reply
  30. julian

    October 18, 2017 at 7:50 am

    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.

    Reply
    • Mynda Treacy

      October 18, 2017 at 9:02 am

      Thanks, Julian! Glad you discovered something new and useful with the data validation ‘comment’.

      Reply
  31. Mickael Taieb

    October 18, 2017 at 6:54 am

    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

    Reply
    • Mynda Treacy

      October 18, 2017 at 9:05 am

      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

      Reply
  32. jim

    October 17, 2017 at 9:18 pm

    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

    Reply
    • Mynda Treacy

      October 17, 2017 at 10:05 pm

      Thanks for sharing your tips, Jim. The more the merrier 🙂

      Reply
  33. Thomas H.

    October 17, 2017 at 9:17 pm

    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.

    Reply
    • Mynda Treacy

      October 17, 2017 at 10:04 pm

      Lucky you, Thomas. Here in Australia, Excel is more fussy 🙂 Thanks for sharing.

      Reply
      • Badri

        July 25, 2019 at 11:45 pm

        How to change month and date only in 29/12/2018 6:00 using formula if they are in the same cell

        Reply
        • Catalin Bombea

          July 26, 2019 at 1:18 pm

          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

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x