The problem is from the 1900/1904 date systems that Excel supports. Excel for mac defaults to 1/1/1904 as its date zero, while Excel for windows defaults to 1/1/1900 as its date Zero The difference is 4 years, or, as you have found out, 1462 days. How Excel Stores Dates. Excel stores dates internally as serial numbers, which are floating-point numbers. The integer portion represents the number of days since January 1, 1900 in Windows, and the number of days since January 1, 1904 on a Mac, where January 1 is equal to one.
I have Excel 2008 installed on my Mac, which I recently upgraded to Mavericks, migrating all my settings across. Previously (on Mountain Lion) when I entered a date like 11/12 this was interpreted as 11 December, but now Excel insists on interpreting this in American format. All my existing data (some going back years, originally entered in Windows) displays correctly as I have entered a custom Format ddd dd/mm/yy, but displays incorrectly in the formula bar.
As far as I can tell, my system settings are correct for Australia - they were migrated across, and I have since checked. Numbers seems to have no problem with dates. After some searching and trial-and-error, I have identified that the issue relates to the absence of a key file: com.apple.HIToolbox.plist, which for some reason is not created when setting up a new User account (and may not be copied across if importing from another computer). Luckily this file is created when opening the Guest account, which can then be copied across to all required User accounts. Here is a detailed summary of the procedure I eventually used to fix this date format issue:.
![For For](https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/main/exceljet_today_01.png?itok=1yqT8Cvr)
Create and open a Guest Account (if not already enabled, go to Users & Group Preferences). Go to the Library folder (it’s a hidden folder, so to find it, use Finder to go to the home folder, then select from the menu ‘Go:Go to Folder’ and type in ‘Library’).
Go to the Preferences folder. Make a local copy of the file: com.apple.HIToolbox.plist (e.g. Onto an SD card, or external/cloud drive). Now open your desired User account. Go to your Library folder (again it will be hidden, so find it as above). Go to the Preferences folder. Copy the file: com.apple.HIToolbox.plist (from local storage) into this folder.
Quit Excel and then log out of your user account, and then log back in. Next time Excel starts, UK date format will be correct!
The tutorial shows how to insert today's date in your worksheets and demonstrates a few other uses of TODAY function in Excel. You want to put today's date in some cell? Or you wish to highlight the current date in your Excel calendar? Or you'd like to find the date closest to today? All this can be done by using the Excel TODAY function and this tutorial will teach you how.
Excel TODAY function - syntax and basic uses The TODAY function in Excel does exactly what its name indicates - returns today's date. The syntax of the TODAY function is as simple as it could possible be - it does not have any arguments at all. Whenever you need to insert today's date in Excel, just enter the following formula in a cell: =TODAY You can format the value returned by TODAY in any built-in or custom.
For example, this way: 4 things you should know about TODAY in Excel. TODAY is a volatile function, which means that it updates itself continuously every time a worksheet is opened or changed.
If a TODAY formula does not update automatically, most likely is turned off in your workbook. To turn it on again, go to the Formulas tab Calculation Options, and select Automatic. To enter today's date in Excel as a static unchangeable value, use.
If you want to insert current date and time, use the NOW function instead of TODAY. How to insert today's date in Excel There are two ways to enter the current date in Excel - a formula and shortcut. Which one to use depends on whether you want a static or dynamic value.
Excel formula for today's date The value returned by the TODAY function updates automatically, so the below formula is useful if you want the worksheet to always display the current date, regardless of when you open it. =TODAY To clarify what kind of date that is, you can concatenate TODAY with some explanatory text, for example: =CONCATENATE('Today is ',TEXT(TODAY, 'mmmm dd, yyyy')) Because in the internal Excel system dates and times are stored as numbers, concatenating text with the TODAY formula directly would result in a meaningless string like 'Today is 42965'. To avoid this, we nest Excel's TODAY function within the to display the date in the desired format. Shortcuts to get today's date in Excel The inset today's date as an unchangeable timestamp that won't automatically update the next day, use one of the following keyboard shortcuts:. To inset today's date: Ctrl +;. To insert the current time: Ctrl + Shift +;.
To enter the current date and time: Ctrl +; then Space and then Ctrl + Shift +; How to use TODAY function in Excel - formula examples As you already know, the main purpose of the Excel TODAY function is to get today's date. Aside from that, you can use TODAY in combination with other functions to perform more complex calculations based on the current date. Below you will find a few examples of such formulas. Add or subtract days to/from today's date To add or subtract a specific number of days to/from the current date, use a simple arithmetic operation of addition or subtraction, respectively. For example, to add 7 days to todays' date, use this formula: =TODAY+7 To subtract 7 days from the current date, go with this one: =TODAY-7 To exclude weekends (Saturday and Sunday) from your calculations, nest the TODAY function within that deals with weekdays only: To add 7 workdays to today's date: =WORKDAY(TODAY, 7) To subtract 7 workdays from today's date: =WORKDAY(TODAY, -7) The following screenshot shows the results. Date-TODAY The date can be supplied directly to a formula in the format that Excel can understand, or by using the DATE function, or as a reference to the cell containing the date. For example, to find out how many days are left till December 31, 2017, use one of these formulas: =A2-TODAY =DATE(2017,12,31)-TODAY ='-TODAY All three formulas tell us that at the moment of writing (August 17, 2017), 136 days were left till the end of the year 2017: To calculate the number of days since a certain date, subtract the past date from today's date.
DATEDIF(TODAY, future-date,'m') With the date of interest in cell A4, use the following formulas to calculate time difference in the number of complete months: To calculate months since a certain date in the past: =DATEDIF(A4,TODAY,'m') To calculate months before a certain date in the future: =DATEDIF(TODAY,A4,'m') Calculate years since / before a certain date The formulas to calculate years based on today's date are similar to the ones discussed in the above example. The difference is that you use 'y' unit to get the number of complete years between today and another data: To calculate years since a past date. YEAR( TODAY)- yearofbirth For example, if the person was born in 2000, you use the following formula to get his/her age: =YEAR( TODAY)-2000 Or, you can enter the year of birth in a separate cell and reference that cell in your formula: You can learn a few other age calculation formulas in this tutorial:. Find the nearest date to today If you are curious to know which date in a list is closest to today's date, use one of the following to find it out. Get a past date closest to today To find a past date nearest to the current date, first 'filter out' the dates greater than today, and then use the MAX function to get the largest date among the remaining ones.
INDEX( range, MATCH(MIN(ABS( range - TODAY)), ABS( range - TODAY), 0)) Here's how this generic formula works:. MIN(ABS( range -TODAY)) part. First, you subtract today's date from each date in the range of dates. Then, the ABS function returns the differences as absolute values without regard to their sign.
The MIN finds the minimal value, which goes to INDEX MATCH as the lookup value. ABS( range -TODAY) part. You subtract today's date from the range of dates and return an array of absolute values. This array is where INDEX MATCH searches for the lookup value. In this example, the formula takes the following shape: =INDEX($A$2:$A$10, MATCH(MIN(ABS($A$2:$A$10 - TODAY)), ABS($A$2:$A$10 - TODAY), 0)) The screenshot below shows the result. All three formulas to get the nearest date are array formulas, so they should be completed by pressing Ctrl + Shift + Enter. How to highlight today's date in Excel When working with a long list of dates or designing your own calendar in Excel, you may want to have the current date highlighted.
![For For](http://www.rondebruin.nl/win/images/calendar.png)
To have it done, create a conditional formatting rule with this formula: =B2=TODAY Where B2 is the left-top-most cell of the range to which the rule applies. The result may look something similar to this: The detailed steps to set up a conditional formatting rule can be found here:. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our. More examples of TODAY formula in Excel For more examples of using the TODAY function in Excel, please check out the following tutorials:.