Examples of Formulas for Converting Date Formats in Excel

In Excel, all dates start from January 1, 1900, and are hidden as subsequent numbers, representing the number of days from that date. For example, the date January 1, 1900, corresponds to the number 1, and the next day, January 2, 1900, corresponds to the number 2, and so on. This method of hiding numbers behind dates is called the 1900 system. This system is the default in all Microsoft Office programs.



How to Get the Current Date and Time in Excel

Instead of manually entering the current date and time, you can use one of two Excel functions. The =TODAY() function returns the current date, and the =NOW() function returns the current date and time:

Both functions do not require any arguments. Both TODAY and NOW functions return a number representing the current system date and time. The TODAY function considers the time as 0:00, while NOW returns the specific time in the Windows operating system at the current moment:

Keep in mind that both functions automatically recalculate whenever there is a change on the worksheet or when it is opened. Therefore, they cannot be used as a static time value when recording data. This fact should be taken into account when optimizing program and system computational resource usage.

If you need to quickly enter a static, unchanging current date using the keyboard, press the hotkey combination CTRL+; (semicolon on the English keyboard layout). The current date will be entered immediately into the active cell. To enter the current time, press the keyboard shortcut CTRL+SHIFT+;.

For a more readable representation of the current date, you can use the TODAY function in a formula along with text. It is better to place it as the first argument for the TEXT function and specify the corresponding format for displaying the value. The formula to display today's date in the format of day, month, and year:

="Today "&TEXT(TODAY(),"dddd d MMMM yyyy")&" - year"

Similarly, you can display any date and time:

Simply remove the TODAY function from the formula and instead enter static values manually or specify a reference to the cell with the date and time.

Formula Example for Converting Date to Roman Numerals in Excel

For converting the format of today's date, you may not always be able to use cell formatting or the TEXT function. For example, if you need to convert the date to Roman numerals, you will have to construct your own formula:

=ROMAN(DAY(A2)) & "." & CHOOSE(MONTH(A2), "I","II","III","IV","V","VI","VII","VIII","IX","X","XI","XII") & "." & ROMAN(YEAR(A2))

This way, you can practically apply and combine formats with formulas for labeling date data:

Download

Working with dates in Excel is very common, so it is important to explore all the possibilities of functions and value conversion formats.