How to distinguish between a value and a date

[Swaroon]

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

 

[SeniorNewbie]

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

 

[FormR]

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
	G	H
34	45454	Batch 45454
35	01/10/2023	Batch 01-Oct-2023
36	123	Batch 123
37	01/10/2023	Batch 01-Oct-2023
Sheet1

Cell Formulas
Range	Formula
H34:H37	H34	="Batch " & IF(LEFT(CELL("format",G34),1)="D",TEXT(G34,"DD-MMM-YYYY"),G34)

 

[Swaroon]

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,

 

[Swaroon]

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
	G	H
34	45454	Batch 45454
35	01/10/2023	Batch 01-Oct-2023
36	123	Batch 123
37	01/10/2023	Batch 01-Oct-2023
Sheet1

Cell Formulas
Range	Formula
H34:H37	H34	="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

 

[SeniorNewbie]

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

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!

 

[Swaroon]

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

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

 

[SeniorNewbie]

you're welcome