Microsoft Office Forums Format in a formula month and numbers

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-27-2019, 04:03 PM
Marcia's Avatar
Marcia Marcia is offline Format in a formula month and numbers Windows 7 32bit Format in a formula month and numbers Office 2007
Competent Performer
Format in a formula month and numbers
 
Join Date: May 2018
Location: Philippines
Posts: 208
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 Format in a formula month and numbers Windows 10 Format in a formula month and numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
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's Avatar
Marcia Marcia is offline Format in a formula month and numbers Windows 7 32bit Format in a formula month and numbers Office 2007
Competent Performer
Format in a formula month and numbers
 
Join Date: May 2018
Location: Philippines
Posts: 208
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 Format in a formula month and numbers Windows 10 Format in a formula month and numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
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 Format in a formula month and numbers Windows 10 Format in a formula month and numbers Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
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 Format in a formula month and numbers Windows 7 64bit Format in a formula month and numbers Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,396
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 Format in a formula month and numbers Windows 8 Format in a formula month and numbers Office 2016
Expert
 
Join Date: May 2017
Posts: 463
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's Avatar
Marcia Marcia is offline Format in a formula month and numbers Windows 7 32bit Format in a formula month and numbers Office 2007
Competent Performer
Format in a formula month and numbers
 
Join Date: May 2018
Location: Philippines
Posts: 208
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 Format in a formula month and numbers Windows 8 Format in a formula month and numbers Office 2016
Expert
 
Join Date: May 2017
Posts: 463
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 Format in a formula month and numbers Windows 8 Format in a formula month and numbers Office 2016
Expert
 
Join Date: May 2017
Posts: 463
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's Avatar
Marcia Marcia is offline Format in a formula month and numbers Windows 7 32bit Format in a formula month and numbers Office 2007
Competent Performer
Format in a formula month and numbers
 
Join Date: May 2018
Location: Philippines
Posts: 208
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
Format in a formula month and numbers Formula For Displaying Exact Days of the Month ddhawks Excel 2 07-12-2016 11:48 PM
Format in a formula month and numbers 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 02:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft