Solved

Format as Dates

Posted on 2011-09-27
5
188 Views
Last Modified: 2012-05-12
Experts,

I have a formula: =B7 & " - " &B8
the cells B7 and B8 are date formats.
I need the formula to return the date format as well but it returns the date as a number.  

How can I change the format from that number that is displaying to a date format of DD-MMM-YY?

thank you
0
Comment
Question by:pdvsa
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 36709361
I think this will work.

= TEXT(B7,"DD-MMM-YY") & " - " & TEXT(B8,"DD-MMM-YY")

Kyle
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36709377
I'm a little confused, for two reasons.

First, the formula you are using is a "concatenation" method, meaning no mathematical calculations should actually be being performed - the result should be showing Date1 - Date2, but dates would be converted to number format.

Second, can you give an example of what date value you would expect to be returned if in cell B7 was 1/2/2011 and in cell B8 was 1/1/2011?  One day between the dates.  What is your expected outcome in your formula cell?
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36709386
Ah, I see, using Kyle's method.  I thought you wanted just one date returned!  Silly me.
0
 

Author Closing Comment

by:pdvsa
ID: 36709396
that was it.  thank you
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36709408
You're welcome.  Thanks for the points and the grade!

Kyle
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now