How to distinguish between a value and a date

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
288
Office Version
  1. 365
Hi
I have a column which is a reference and could include dates, texts and numbers as the reference.
I need to concatenate as "BATCH "&cell reference

Example of data in reference column:
45454
01/10/2023, giving

BATCH 45454
BATCH 01/10/2023

="BATCH "&TEXT(G34,"DD-MMM-YY") gives me a date format when a date but will then also date format the 45454.

Is it possible to distinguish in a formula between when a number is formatted as a date or formatted as a number to give the above result ?

Thanks
Steve
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,
just use VBA to format the 01/10/2023 to text. If you show a workbook with some sample lines, it's easy to do.
Best wishes Senior
 
Upvote 0
Is it possible to distinguish in a formula between when a number is formatted as a date or formatted as a number to give the above result ?
Hi, you could give this a try, but note, I'm not sure how robust it would be with regional settings different to the UK.

Book6
GH
3445454Batch 45454
3501/10/2023Batch 01-Oct-2023
36123Batch 123
3701/10/2023Batch 01-Oct-2023
Sheet1
Cell Formulas
RangeFormula
H34:H37H34="Batch " & IF(LEFT(CELL("format",G34),1)="D",TEXT(G34,"DD-MMM-YYYY"),G34)
 
Upvote 0
Hi
I have a column which is a reference and could include dates, texts and numbers as the reference.
I need to concatenate as "BATCH "&cell reference

Example of data in reference column:
45454
01/10/2023, giving

BATCH 45454
BATCH 01/10/2023

="BATCH "&TEXT(G34,"DD-MMM-YY") gives me a date format when a date but will then also date format the 45454.

Is it possible to distinguish in a formula between when a number is formatted as a date or formatted as a number to give the above result ?

Thanks
Steve

Hi,
just use VBA to format the 01/10/2023 to text. If you show a workbook with some sample lines, it's easy to do.
Best wishes Senior
Hi, thanks for getting back,

1697119864489.png
 
Upvote 0
Hi, you could give this a try, but note, I'm not sure how robust it would be with regional settings different to the UK.

Book6
GH
3445454Batch 45454
3501/10/2023Batch 01-Oct-2023
36123Batch 123
3701/10/2023Batch 01-Oct-2023
Sheet1
Cell Formulas
RangeFormula
H34:H37H34="Batch " & IF(LEFT(CELL("format",G34),1)="D",TEXT(G34,"DD-MMM-YYYY"),G34)

Thanks for the formula, works great for me in UK, will see how the US users get on but thanks
 
Upvote 0
Hi, copy this code in a test worksheet, costumize it to your columns. To find out your local date format use the DoubleClick.

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox Target.NumberFormat
End Sub

Sub DateToText()
Dim i As Integer, j As Integer, r As Integer, c As Integer

r = UsedRange.Rows.Count
c = UsedRange.Columns.Count

For i = 1 To r
    For j = 1 To c
        If Cells(i, j).NumberFormat = "m/d/yyyy" Then
            Cells(i, c + 1).NumberFormat = "@"
            Cells(i, c + 1) = "Batch " & CStr(Cells(i, j)) & " " & Cells(i, 2)
        End If
    Next j
Next i
Columns.AutoFit
End Sub

Have fun!
 
Upvote 0
Hi, copy this code in a test worksheet, costumize it to your columns. To find out your local date format use the DoubleClick.

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox Target.NumberFormat
End Sub

Sub DateToText()
Dim i As Integer, j As Integer, r As Integer, c As Integer

r = UsedRange.Rows.Count
c = UsedRange.Columns.Count

For i = 1 To r
    For j = 1 To c
        If Cells(i, j).NumberFormat = "m/d/yyyy" Then
            Cells(i, c + 1).NumberFormat = "@"
            Cells(i, c + 1) = "Batch " & CStr(Cells(i, j)) & " " & Cells(i, 2)
        End If
    Next j
Next i
Columns.AutoFit
End Sub

Have fun!
Thanks, the file uses macros so will take a look, appreciate the help
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top