Change string date format based on contents of another column

[rp1975]

Hello

We have a sheet that includes the date in custom date format (13/03/2018 8:15:00 AM)
We are using the below code to convert the date to different format and into text (Day of the week is hidden at this time)

<CODE>
Dim c As Range

For Each c In ws.UsedRange.Columns("C").Cells
' strDay = Format(c.Value, "dddd")
strDate = Format(c.Value, "YYYY/MM/DD")



'24 Hour time
strTime = Format(c.Value, "HH:mm")
c.NumberFormat = "@"
c.Value = strDate & Space(2) & "(yyyy/mm/dd)" & Space(35) & "at / Ã*" & Space(1) & strTime
Next c
<Code>
Now the issue

Based on the value in Column M (EN or FR) we want to change the strTime format

For FR we want the above strTime = Format(c.Value, "HH:mm")

for EN we want 'strTime = Format(c.Value, "HH:mm am/pm")

I assume we would need to update the format to one then loop thru all the populated cells in column M and then change the format but we have no idea where to even start.

 

[mole999]

If you have a marker then this might work for you

   If ActiveSheet.Range("A1") = "EN" Then Columns("C:C").NumberFormat = "h:mm AM/PM"
   If ActiveSheet.Range("A1") = "FR" Then Columns("C:C").NumberFormat = "h:mm"

 

[rp1975]

The field we are updating includes the date and time so the solution does not appear to work

Thanks for the help

 

[mole999]

if it is returning the correct time then just add the dd/mm/yy you require

 

[rp1975]

Sorry My question was not worded correctly or I am just dumb.

Column C includes custom cell format of (13/03/2018 1:15:00 AM) (I changed the time in the question to show difference)
We are using the below loop below to Extract the Date and time then place them back into the cell as Text format. (one by one )
During this process we want to compare the value of Column "M" for that row (EN or FR)
If the Cell contains "EN" we want the 'strTime = Format(c.Value, "HH:mm am/pm") then in the second to last line of the code it would populate cell "C" to show (13/03/2018 (yyyy/mm/dd) at / Ã 8:15 AM
If the Cell contains "FR" we want the 'strTime = Format(c.Value, "HH:mm")then in the second to last line of the code it would populate Cell "C" to show (13/03/2018 (yyyy/mm/dd) at / Ã 13:15

<Code>

Dim c As Range

For Each c In ws.UsedRange.Columns("C").Cells
' strDay = Format(c.Value, "dddd")
strDate = Format(c.Value, "YYYY/MM/DD")



'24 Hour time
strTime = Format(c.Value, "HH:mm")
c.NumberFormat = "@"
c.Value = strDate & Space(2) & "(yyyy/mm/dd)" & Space(35) & "at / Ã*" & Space(1) & strTime
Next c

<Code>

Or

Would it be better to set two strTime (ie: strTime24 and strTime12)
Then use a if statement on C.value line (This doesn't work as I have the format wrong)


<Code>

Dim c As Range

For Each c In ws.UsedRange.Columns("C").Cells
' strDay = Format(c.Value, "dddd")
strDate = Format(c.Value, "YYYY/MM/DD")

' 12 Hour Time
strTime12 = Format(c.Value, "HH:mm am/pm")

'24 Hour time
strTime24 = Format(c.Value, "HH:mm")
c.NumberFormat = "@"


If (Cells(i, "M").Value) = "EN" Then
Cells(i, "C").c.Value = strDate & Space(2) & "(yyyy/mm/dd)" & Space(35) & "at / Ã*" & Space(1) & strTime24
Else: Cells(i, "C").c.Value = strDate & Space(2) & "(yyyy/mm/dd)" & Space(35) & "at / Ã*" & Space(1) & strTime12
End If

Next c

<Code>

 

[mole999]

strtime12 and 24 use the same decimal value, its a matter of how the cell is formatted at the end as to what display you show

would this work

strDate & " " & "(yyyy/mm/dd)" & " 35 SPACES " & "at / Ã*" & " " & Format(strTime, "hh:mm")

untested

 

[rp1975]

Thanks Mole999 for all your help on this...

My last try on this then I am giving up

I have the formatting to change for the whole column but the underlining issues still remains in

During the For / Next loop that is shown above in the example we need to check the value in "M" Column (As it could be one of the two values) and update the strTime for that row only then populate the "C" cell of the row with the full test string
Then repeat on the next row until the end of data in column C

Below example is the first 3 rows in the sheet and then the wanted result ( Start Format is "C" =Custom Format and "M" = Text) (Output need to be all in Text format)
When the For / Next loop runs it needs to take the data from Column "C" Row 1 covert to strings check column "M" and format the strTime into 12H time Based on the EN in Column "M" then put the output back into Row 1 of Column "C" (As seen in output ) then it does the same steps on Row 2 except now that "M" is FR change for the Strtime format to 24H (As seen in output)

Input
[TABLE="width: 373"]
<colgroup><col width="375" style="width: 281pt; mso-width-source: userset; mso-width-alt: 13714;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody>[TR]
[TD="class: xl66, width: 375, bgcolor: #E8E4DC"]"C"[/TD]
[TD="class: xl66, width: 123, bgcolor: #E8E4DC"]"M"[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]13/02/2018 1:15:00 PM[/TD]
[TD="class: xl68, bgcolor: transparent"] EN[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]13/02/2018 1:15:00 PM[/TD]
[TD="class: xl68, bgcolor: transparent"] FR[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]13/02/2018 1:15:00 PM[/TD]
[TD="class: xl68, bgcolor: transparent"] EN[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Output[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2018/02/21 (yyyy/mm/dd) at / Ã* 1:15 PM[/TD]
[TD="class: xl68, bgcolor: transparent"]EN[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2018/02/21 (yyyy/mm/dd) at / Ã* 13:15[/TD]
[TD="class: xl68, bgcolor: transparent"]FR[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2018/02/21 (yyyy/mm/dd) at / Ã* 1:15 PM[/TD]
[TD="class: xl68, bgcolor: transparent"]EN[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

I got it to this, but its not right

Sub test()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
Dim n
For n = 1 To LastRow
Dim strtwo
Dim strvalue
strvalue = Range("c" & n).Value
strtwo = strvalue
Dim a
Dim b
Dim d
a = Format(strtwo, "yyyy/mm/dd")
b = Format(strtwo, "hh:mm")
d = Format(strtwo, "h:mm AM/PM")
If Range("M" & n) = "EN" Then Range("c" & n) = a & " " & "(yyyy/mm/dd)" & "                                  " & "at / Ã*" & " " & b
If Range("M" & n) = "FR" Then Range("c" & n) = a & " " & "(yyyy/mm/dd)" & "                                  " & "at / Ã*" & " " & c
Next n
End Sub

French time disappears

 

[mole999]

you need to test C as well

IF (MID(C1,12,1)) = "(" next n

as it will just keep adding again and again