#1




Format in a formula month and numbers
Hi all, below is a formula concatenating values from 2 columns. It returns a string like 201911. I would like the result to appear as 201901001. 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. Code:
=YEAR([@Date])&""&MONTH([@Date])&""&[@ObR] 
#2




With date in A1
=DATE(YEAR(A1),MONTH(A1),DAY(A1)) Format the cell with the formula: yyyymm"00"d 
#3




Quote:
A1: January 18, 2019 (named range "Date") B1: 1 (named range "ObR") C1 where the formula is: 201901001 
#4




C1 where the formula is: 201901001 ...
Not understood. 
#5




Is it like this

#6




As dates can cause problems, it's always best to post a small sample sheet
It saves the person helping time
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank. 
#7




Code:
=TEXT(Date,"yyyymm") & RIGHT("000" & Obr,3) 
#8




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, 201901001. I need to explore further the function of the two negatives before the formula. I tried deleting the negatives but the formula returned 201911.

#9




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

#10




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

#11




Quote:
I love this forum. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Need Help Deleting Numbers in Hour Format and Numbers in Sequence  rsrasc  Word VBA  1  10122016 02:49 PM 
Formula For Displaying Exact Days of the Month  ddhawks  Excel  2  07122016 11:48 PM 
Formula to divide if no of days in month is 28  shilabrow  Excel  5  06262014 12:17 AM 
Formula to count entries as per month, year and submission wise from different sheets  pinkacidpunk  Excel  1  05282013 08:26 AM 
Formula to subtract one month from due date field in reminder field  ghumdinger  Outlook  1  10012011 12:09 AM 