• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

function format [SOLVED]

A

Afarag

Member
Dears

after appreciate your great efforts, I want help

if i use a function as the explained below

="Dear"""&G4&"""you have to check your"""&G5&"that will end at"&VLOOKUP(G5,A4:C8,3,0)

and the vlookup value is a time, how can i formate it in this fuction, be informed that

the main value is formatted, and the function appear it 0


thanks a lot
 
so just i understand... when the vlookup that you're using fetches the respective cell, it returns a 0? instead of the date(formatted)?


one idea; and i dont know how easy this would.... if you go to wherever the dates are stored, and instead of formatting the cells to display a certain way, you re-enter the dates in that specific format.... so in A1 i could be 4-13-13 and simply format it to display as "14, April 13". what im saying is if you enter it in that cell in the format you want rather than applying the format (hope that makes sense)

--(instead of re-typing, you could setup a new col. with the TEXT function. the first part would point to a cell with the first date, then the 2nd part would be whatever format you wanted the date to appear as. EX: =TEXT(A1,"DD-MMM,YY" this would look at a1 (4/13/13) and return 13-April,13. THEN you apply that to all your dates. and finally you could copy the whole col and paste the values. in short, you just converted your number into a text that looks like a date. ....after that your vlookup may work


hahahahha
 
Hi, Afarag!


Excel treats dates as integer numbers starting at 01/01/1900 with the value of 1 so today 31/05/2013 is 41425, and times as decimal numbers from 0 corresponding to 00:00:00 to near 1 (0,999988425925926) corresponding to 23:59:59. Now it's 41425,7512395833.


About your question you could try this:

="Dear"""&G4&"""you have to check your"""&G5&"that will end at"&TEXT(VLOOKUP(G5,A4:C8,3,0),"hh:mm:ss")

You could adjust the format 2nd parameter to the string you require.


Regards!
 
Hi, Afarag!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top