Change string date format based on contents of another column

rp1975

New Member
Joined
Feb 16, 2018
Messages
4
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
If you have a marker then this might work for you
Code:
   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"
 
Upvote 0
The field we are updating includes the date and time so the solution does not appear to work

Thanks for the help
 
Upvote 0
if it is returning the correct time then just add the dd/mm/yy you require
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
I got it to this, but its not right
Code:
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
 
Last edited:
Upvote 0
you need to test C as well

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

as it will just keep adding again and again
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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