Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page if cell contains date, then month, else blank
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 10-03-2016, 07:58 AM
Merlot Merlot is offline if cell contains date, then month, else blank Windows 7 64bit if cell contains date, then month, else blank Office 2010 64bit
Novice
if cell contains date, then month, else blank 		 
Join Date: Dec 2015
Posts: 7
Merlot is on a distinguished road
Default if cell contains date, then month, else blank

I am trying to input a formula for if a cell contains a date then return the month's number else leave the cell blank.

I tried this: =IF(A37=(mmmm,dd,yyyy),MONTH(A37),"")

The date in A37 is in this format : August 4, 2016

Thanks
Reply With Quote
  #2  
Old 10-03-2016, 08:56 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline if cell contains date, then month, else blank Windows 7 64bit if cell contains date, then month, else blank Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,915
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
What else can the cell contain?
__________________
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
Reply With Quote
  #3  
Old 10-03-2016, 09:22 AM
Merlot Merlot is offline if cell contains date, then month, else blank Windows 7 64bit if cell contains date, then month, else blank Office 2010 64bit
Novice
if cell contains date, then month, else blank 		 
Join Date: Dec 2015
Posts: 7
Merlot is on a distinguished road
Default
It can either contain the date, text or be blank
Reply With Quote
  #4  
Old 10-03-2016, 11:49 AM
Merlot Merlot is offline if cell contains date, then month, else blank Windows 7 64bit if cell contains date, then month, else blank Office 2010 64bit
Novice
if cell contains date, then month, else blank 		 
Join Date: Dec 2015
Posts: 7
Merlot is on a distinguished road
Default
Hi,

I tried this =IF(OR(A37="",ISTEXT(A37)),"",MONTH(A37)) and it is working. Thanks though.
Reply With Quote
  #5  
Old 10-03-2016, 03:27 PM
jeffreybrown jeffreybrown is offline if cell contains date, then month, else blank Windows Vista if cell contains date, then month, else blank Office 2007
Expert
  
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default
Try...

=IF(N(A37),MONTH(A37),"")
Reply With Quote
  #6  
Old 03-19-2025, 03:54 AM
ellocco ellocco is offline if cell contains date, then month, else blank Windows 7 64bit if cell contains date, then month, else blank Office 2016
Novice
  
Join Date: Mar 2025
Posts: 1
ellocco is on a distinguished road
Default verify if cell contains a date incl. time
I managed to verify the content via the function: "CELL":
=CELL("format", A1)
Gives an output dependent the cell content.
I expect something like: DD.MM.YYYY hh:mm:ss
and this correspond to the function output: "D1"
A cell format of "hh:mm:ss" gives: "U4"
It might be that the function is sensitive to your localization,
I have tested it with a German localization, the German variant is:
'ZELLE("format";B18)
Reply With Quote
  #7  
Old 03-19-2025, 12:19 PM
ArviLaanemets ArviLaanemets is offline if cell contains date, then month, else blank Windows 8 if cell contains date, then month, else blank Office 2016
Expert
  
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default
What is really the value in cell A37?
Is it a date, and the cell is formatted as "mm,dd,yyyy"?
When yes, then (as dates in Excel really are positive numbers)
Code:
=IF(A37>0, MONTH(A37), "")
Or is it a datestring either entered/copied into call manually, or the result of FORMAT(function)?
When yes, then (assumed "mm,dd,yyyy" is a valid date format for your local settings, but you can handle non-valid values with IFERROR() when there is a possibility for it)
Code:
=IF(IFERROR(DATEVALUE(A37),0)>0, MONTH(A37), "")
Reply With Quote
  #8  
Old 03-20-2025, 08:16 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline if cell contains date, then month, else blank Windows 11 if cell contains date, then month, else blank Office 2021
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,915
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Quote:
Originally Posted by ellocco View Post
I managed to verify the content via the function: "CELL":
=CELL("format", A1)
Gives an output dependent the cell content.
I expect something like: DD.MM.YYYY hh:mm:ss
and this correspond to the function output: "D1"
A cell format of "hh:mm:ss" gives: "U4"
It might be that the function is sensitive to your localization,
I have tested it with a German localization, the German variant is:
'ZELLE("format";B18)
FYI this thread is 9 years old...
__________________
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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
if cell contains date, then month, else blank Auto update excel graph range, ignore data when date reads 00-Month-00 SavGDK Excel 2 06-24-2016 08:05 AM
Excel day date month year Function greginky Excel 1 01-05-2016 02:19 AM
if cell contains date, then month, else blank Filter by a Month that falls within a date range MattG1225 Excel 2 12-03-2015 07:44 AM
if cell contains date, then month, else blank Formulato say if cell is blank do this, if not blank do this. mbesspiata Excel 1 01-17-2015 05:02 AM
calculate date if date entered in cell, do nothing if blank ConfuddledOne Excel 3 11-07-2014 09:37 AM

Other Forums: Access Forums

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

Contact Us - Privacy Statement - Top

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