CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
John Smith
John Smith

Reputation: 138

Changing format of TODAY() in excel

I'm using today to aquire todays date and then adding a static value to the end of it using the following:

=TODAY()&"T23:00:00"

Which Returns 43202T23:00:00

I really need it in the format 2018-04-12T23:00:00

Any help on this would be great!

Upvotes: 3

Views: 8546

Answers (1)

ashleedawg
ashleedawg

Reputation: 21629

There are a couple ways to accomplish this, depending on whether your goal is a formatted String (to display) or a numeric value (such as data type Date) for storing or using with calculations.

If you want a formatted date/time result (to display to the user)...

Use the TEXT worksheet function:

=TEXT(TODAY(),"yyyy-mm-dd")&"T23:00:00"

...the reason this works is because TODAY() returns a Date data type, which is basically just a number representing the date/time, (where 1 = midnight on January 1, 1900, 2 = midnight on January 2, 1900, 2.5 = noon on January 2, 1900,etc).

You can convert the date type to a String (text) with the TEXT function, in whatever format you like. The example above will display today's date as 2018-04-12.

If, for example, you wanted the date portion of the string displayed asApril 12, 2018 then you would instead use:

TEXT(TODAY(),"mmmm d, yyyy")

Note that the TEXT worksheet function (and VBA's Format function) always return Strings, ready to be concatenated with the rest of the String that you're trying to add ("T23:00:00").

If you want to use the result in calculations...

If you instead want the result to be in a Date type, then instead of concatenating a string (produced by the TEXT function) to a string (from "T23:00:00"), you could instead add a date to a date:

=TODAY()+TIME(23,0,0)

or

=TODAY()+TIMEVALUE("23:00")

..and then you can format it as you like to show or hide Y/M/D/H/M/S as necessary with Number Formats (shortcut: Ctrl+1).

More Information:

Upvotes: 6

Related Questions