Hi all, below is a formula concatenating values from 2 columns. It returns a string like 2019-1-1. I would like the result to appear as 2019-01-001. I tried inserting TEXT before the month and number but I always come up with "too many arguments" message. Kindly check my formula. Thank you.

With date in A1

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Format the cell with the formula: yyyy-mm-"00"d

Thank you Xor for the immediate reply. The formula should return the year and month from A1, after the month is a serial number taken from B1, like:


A1: January 18, 2019 (named range "Date")

B1: 1 (named range "ObR")

C1 where the formula is: 2019-01-001

C1 where the formula is: 2019-01-001 ...

Not understood.

Is it like this

Attached Files

Format_Day.xlsx (9.9 KB, 12 views)

As dates can cause problems, it's always best to post a small sample sheet
It saves the person helping time

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Noted, Pecoflyer about the sample data. Thank you Xor and Arvi. I got confused by the "DAY" in both of your formula and when I removed it just leaving the "ObR" as the reference, the result is still the desired format, 2019-01-001. I need to explore further the function of the two negatives before the formula. I tried deleting the negatives but the formula returned 2019-1-1.

--(Logical Expression) converts boolean value of expression (True or False) to numeric (1 or 0)

Probably Xor assumed, that @Obr is day number! Set the value for @Obr e.g. to 99, and look what happens!

Number 32 onwards gave error values. The moderator is right in his reminder for OPs to attach samples to avoid time wasting and oftentimes erroneous assumptions.
I love this forum.