Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()

Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)

Column c: =HOUR(A2)

Column d: =minute(A2)

Column e: =Second(A2)

Column F: =TIME(C2,D2,E2)

Do you have any other suggestions for this reader’s question?

How do I use a formula to count the remaining days until the end of the month in Excel?

Using the =DAY(TODAY()) commands I can get the date 2/15/2010 to show that we are on day 15 of the month. I would like the number all the way up to the last day of the month and then stay there. In this example November counted up to 30 days, Dec to 31, Jan to 31, and since today is the 15^th of Feb it is currently on 15. Tomorrow it will be 66. After the 31 I would like it to stop at 31. Is there a possible formula for this?

Month	Days
2009-Nov	30
2009-Dec	31
2010-Jan	25
2010-Feb	15

Yes there is a formula. First, you would need to have a fixed component or refer to a year/month value. Try this for January 2010:

=IF(--TEXT(TODAY(),"yyyymm")>201001,DAY(DATE(2010,2,0)),DAY(TODAY()))

That formula would begin by returning 1 on 01-JAN-2010 and increment by 1
each day up through 31-JAN-2010...then it would stay at 31

February 2010 would be this:

=IF(--TEXT(TODAY(),"yyyymm")>201002,DAY(DATE(2010,3,0)),DAY(TODAY()))

Now, one other thing you can do is take the 201002 numbers and place those in a separate column and then just point to that column in your formulas.

What do the hypens before TEXT do? A leading hyphen causes Microsoft Excel to interpret the contents of a cell as a formula. If you want to use a leading hyphen but not create a formula, use an en dash (option-hyphen) or an em dash (shift-option-hyphen) in place of the hyphen. Excel treats them like any letter or number.

You can also force Excel to treat the contents of any cell as text by typing an apostrophe at the beginning of the cell contents. Alternatively, format the cell as text (choose Cells from the Format menu, click the Number tab, and double-click Text in the category list). After changing a cell to text format, you may have to press command-U to make the change take effect.

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.