Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-27-2019, 04:03 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 181
Marcia is on a distinguished road
Default Format in a formula month and numbers

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.




Code:
=YEAR([@Date])&"-"&MONTH([@Date])&"-"&[@ObR]
Reply With Quote
  #2  
Old 02-27-2019, 09:36 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,018
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

With date in A1

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

Format the cell with the formula: yyyy-mm-"00"d
Reply With Quote
  #3  
Old 02-27-2019, 11:31 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 181
Marcia is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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
Reply With Quote
  #4  
Old 02-27-2019, 11:46 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,018
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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

Not understood.
Reply With Quote
  #5  
Old 02-27-2019, 11:52 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,018
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Is it like this
Attached Files
File Type: xlsx Format_Day.xlsx (9.9 KB, 4 views)
Reply With Quote
  #6  
Old 02-28-2019, 12:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,383
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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.
Reply With Quote
  #7  
Old 02-28-2019, 02:50 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 433
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Code:
=TEXT(Date,"yyyy-mm-") & RIGHT("000" & Obr,3)
Reply With Quote
  #8  
Old 02-28-2019, 04:13 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 181
Marcia is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 02-28-2019, 04:46 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 433
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

--(Logical Expression) converts boolean value of expression (True or False) to numeric (1 or 0)
Reply With Quote
  #10  
Old 02-28-2019, 04:50 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 433
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Probably Xor assumed, that @Obr is day number! Set the value for @Obr e.g. to 99, and look what happens!
Reply With Quote
  #11  
Old 02-28-2019, 05:55 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 181
Marcia is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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.
Reply With Quote
Reply

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 10-12-2016 02:49 PM
Formula For Displaying Exact Days of the Month ddhawks Excel 2 07-12-2016 11:48 PM
Formula to divide if no of days in month is 28 shilabrow Excel 5 06-26-2014 12:17 AM
Formula to count entries as per month, year and submission wise from different sheets pinkacidpunk Excel 1 05-28-2013 08:26 AM
Formula to subtract one month from due date field in reminder field ghumdinger Outlook 1 10-01-2011 12:09 AM


All times are GMT -7. The time now is 05:59 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft