• 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.

Excel Formula

R

Ram Chandran

Member
Hi Guys,

I need a excel function for the below formula which can be apply in all Excel workbooks anytime.
=TEXT(DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)),"DD-MM-YYYY")

Thanks in advance
 
What do you mean? What's wrong with the function you are using?

Attach a workbook with examples.
 
Hi AliGW,

Thanks for the quick response.
the formula which i mention above is works fine.
but i always need to type this full formula each time if i use different excel workbooks.
Can i use this formula for every excel workbook so that i don't want to type each time.
Thank in advance.
 
Wht not just copy and paste it?

Which version of Excel do you have? If you have 365, you might be able to create a LAMBDA function and store it with a suitable function name.
 
Attached is a workbook with a sample LAMBDA function called DateFormat. The LAMBDA is set up in Name Manager (on the Formulas Ribbon).

Details about creating LAMBDAs in this way are here: https://www.ablebits.com/office-addins-blog/excel-lambda-function-write-calculate-use/

From that page:

How to export / import LAMBDA to another workbook​

Like anything defined in Excel's Name Manager, LAMBDA is limited to the workbook it is created in.

Luckily, there is quite an easy way to transfer LAMBDA to another workbook. You simply copy a blank sheet from the old workbook to the new one. As the function was saved in the scope of Workbook, it travels with absolutely any worksheet that you copy or move.

Please note that this method exports absolutely all LAMBDA functions that exist in the original workbook.

Hopefully, these examples have inspired you to look for your own uses of LAMBDA in Excel. For now, let me briefly summarize the key takeaways.
Click to expand...
 

Attachments

  • LAMBDA DateFormat Function for Excel 365 AliGW.xlsx
    9.5 KB · Views: 2
Hi AliGW,

Thanks for the report, but it was not working in office 365.
and also i need to use this formula for all workbooks, and i don't want to copy and paste.
thanks in advance
 
Thanks for the report, but it was not working in office 365.
Click to expand...

It works based on what you told us and was created in 365. It's probably your data or you didn't follow the instructions properly - attach a file showing it not working, please, because I can't troubleshoot it otherwise.

This is the LAMBDA that is in Name Manager under the name DateFormat:

=LAMBDA(range,IFERROR(TEXT(DATE(LEFT(range,4),MID(range,5,2),RIGHT(range,2)),"DD-MM-YYYY"),""))

The range parameter requires that you enter a range (e.g. B2:B14). I presume you aren't trying to run it without entering the range?
 
Excuse me ...
#11 reply - You wrote that is ... number format, BUT ...
I asked ... is it number or text.
# 13 reply - You wrote that is ... text.
BUT
I tried to get valid reply one more time.
> is B2 number or text?
It no matter, what it looks like!
>>>> write any other cell =isnumber(B2)
> do You want B2 after function to be number or text?
 
I asked something else than format.
Did You skip that my given formula?
... as well as ...
... to answer ... after ... number or text?
 

Ram Chandran​

I see
... that Your own sample formula gives something else than in Your sample file.
You've asked something like 26-06-2024, but You need like 26/06/2024 ... hmm?
You can get Your output without that text-function.
Your output cells have to be format like dd/mm/yyyy.
You skipped to confirm: do Your output use like date?
This output is a date ( as well as number ... cells F3 ).
Your: which can be apply in all Excel workbooks anytime.
For me, above means that You could use only very basic old functions, which has used already many years.
 

Attachments

  • Excel Formula.xlsb
    8.7 KB · Views: 1
The attached contains TWO LAMBDAs:

DateFormat

=LAMBDA(range,IFERROR(TEXT(DATE(LEFT(range,4),MID(range,5,2),RIGHT(range,2)),"DD/MM/YYYY"),""))

ProperDate

=LAMBDA(range,IFERROR(DATE(LEFT(range,4),MID(range,5,2),RIGHT(range,2)),""))

The first produces TEXT that looks like a date. The second produces a real date, which is a 5-digit serial number. The second needs its column setting to SHORT DATE format.
 

Attachments

  • LAMBDA DateFormat Function for Excel 365 AliGW.xlsx
    10.3 KB · Views: 1
vletm said:

Ram Chandran​

I see
... that Your own sample formula gives something else than in Your sample file.
You've asked something like 26-06-2024, but You need like 26/06/2024 ... hmm?
You can get Your output without that text-function.
Your output cells have to be format like dd/mm/yyyy.
You skipped to confirm: do Your output use like date?
This output is a date ( as well as number ... cells F3 ).
Your: which can be apply in all Excel workbooks anytime.
For me, above means that You could use only very basic old functions, which has used already many years.
Click to expand...
Thank you so much for your kind replies..
 
AliGW said:
The attached contains TWO LAMBDAs:

DateFormat

=LAMBDA(range,IFERROR(TEXT(DATE(LEFT(range,4),MID(range,5,2),RIGHT(range,2)),"DD/MM/YYYY"),""))

ProperDate

=LAMBDA(range,IFERROR(DATE(LEFT(range,4),MID(range,5,2),RIGHT(range,2)),""))

The first produces TEXT that looks like a date. The second produces a real date, which is a 5-digit serial number. The second needs its column setting to SHORT DATE format.
Click to expand...
Thank you so much for your efforts.
 
You must log in or register to reply here.
Back
Top