Solved

Left formula with IF

Posted on 2011-03-14
13
266 Views
Last Modified: 2012-05-11
Hi,

I have some left formula with an IF thats not quite working.

The cell highlighted red needs to be the date from cell C4.

Can someone have a look and see where the error is?

Thanks
Seamus
test1.xls
0
Comment
Question by:Seamus2626
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35126998
You don't need to use an if formula for that :)

Put this formula there =C4 and then format the cell as mm/dd/yyyy

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35127004
Sample attached.

Sid
Test.xls
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 250 total points
ID: 35127015
Hello Seamus,

since you want the date only when column E is "no", you can use

=IF(E4="No",Int(C4),"")

then format the result cell as date. The INT() will cut off the time portion. Or just use

=IF(E4="No",C4,"")

and format as date without time.

cheers, teylyn
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 10

Expert Comment

by:Makrini
ID: 35127021
or

=IF(E4="No",TEXT(C4,"mm/dd/yyyy"))
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35127023
But for example a formula for that would be:

=IF(E4="No",TEXT(C4,"mm dd yyyy"),"")

Chris
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35127026
Sid means to say no need for LEFT:
=IF(E4="No",LEFT(C4,10),"")
So it becomes:
=IF(E4="No",C4,"")
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35127032
Thanks jimy :)

Yes you are right.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35127136
Seamus: I re looked at the posts. Though I pointed you in the right direction, I believe Teylyn gave you the exact answer and hence the points should have been split between me and her ;)

Sid
0
 

Author Comment

by:Seamus2626
ID: 35127151
Thanks Sid, have requested attention and will reallocate.

Thank you
Seamus
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35127162
Much appreciated :)

Sid
0
 

Author Comment

by:Seamus2626
ID: 35127175
Nope, thank you!

Seamus
0
 

Author Closing Comment

by:Seamus2626
ID: 35127324
Thanks all

Seamus
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

831 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