Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Fix Time conversion

I need Experts help to fix the time conversion script. The script is used to add 8 hours (+8), however the date conversion was not properly updated. e.g cell A2:A4 should updated as 10-Mar, by right should be 9-Mar. I have copied the actual time before conversion at Column F and G for Experts perusal.

Hope Experts could help me to fix this.

Sub ConvertDateandTime()
Dim cel As Range
For Each cel In Range("A2:A" & Range("A2").End(xlDown).Row)
cel.Value = cel.Value + Int(cel.Offset(0, 1) + 8 / 24)
cel.Offset(0, 1) = cel.Offset(0, 1) + 8 / 24 - Int(cel.Offset(0, 1) + 8 / 24)
Next cel
Columns("B:B").Select
Selection.NumberFormat = "hh:mm"

End Sub

Open in new window

Convert-Time.xls
0
Billa7
Asked:
Billa7
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Sub ConvertDateandTime()
Dim cel As Range
For Each cel In Range("A2:A" & Range("A2").End(xlDown).Row)
cel.Offset(0, 1).Value = cel.Offset(0, 1).Value - Int(cel.Offset(0, 1).Value)
cel.Value = cel.Value + Int(cel.Offset(0, 1) + 8 / 24)
cel.Offset(0, 1) = cel.Offset(0, 1) + 8 / 24 - Int(cel.Offset(0, 1) + 8 / 24)
Next cel
Columns("B:B").Select
Selection.NumberFormat = "hh:mm"
End Sub
0
 
Billa7Author Commented:
Thanks a lot
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now