• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Left formula with IF

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
Seamus2626
Asked:
Seamus2626
2 Solutions
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
Sample attached.

Sid
Test.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
MakriniCommented:
or

=IF(E4="No",TEXT(C4,"mm/dd/yyyy"))
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
But for example a formula for that would be:

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

Chris
0
 
jimyXCommented:
Sid means to say no need for LEFT:
=IF(E4="No",LEFT(C4,10),"")
So it becomes:
=IF(E4="No",C4,"")
0
 
SiddharthRoutCommented:
Thanks jimy :)

Yes you are right.

Sid
0
 
SiddharthRoutCommented:
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
 
Seamus2626Author Commented:
Thanks Sid, have requested attention and will reallocate.

Thank you
Seamus
0
 
SiddharthRoutCommented:
Much appreciated :)

Sid
0
 
Seamus2626Author Commented:
Nope, thank you!

Seamus
0
 
Seamus2626Author Commented:
Thanks all

Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now