• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

date / time format issue

Hello all

I have a macro that is sending 2 cells in 1 in another sheet.

this is the code:
 .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12), "hhmm")

Open in new window



Now my problem is that i need to put in .Cells(Derlign, 10) the same date and time but 2hrs earlier from the value in SDD File Master sheet.

I will also have to put in .Cells(Derlign, 11) same date time + 2 hrs

How can i do this please?

 .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12), "hhmm") - format(2, "hh")

Open in new window






Thanks








0
Wilder1626
Asked:
Wilder1626
  • 11
  • 10
1 Solution
 
Saqib Husain, SyedEngineerCommented:
.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12)-2/24, "hhmm")

 .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12)+2/24, "hhmm")
0
 
Saqib Husain, SyedEngineerCommented:
Actually it should be

.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11)-2/24, "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12)-2/24, "hhmm")

.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11)+2/24, "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12)+2/24, "hhmm")
0
 
Saqib Husain, SyedEngineerCommented:
You can simplify it by

.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11)-2/24, "yyyymmdd hhmm")

.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11)+2/24, "yyyymmdd hhmm")

0
Industry Leaders: 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!

 
Wilder1626Author Commented:
Hello ssaqibh

I have an execution error 13

type incompatible.

                .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12) - 2 / 24, "hhmm")


Do you know why?
0
 
Saqib Husain, SyedEngineerCommented:
does it work otherwise?
0
 
Saqib Husain, SyedEngineerCommented:
Can you debug it? Do you get the line highlighted in yellow if you click on debug?

If yes then select this part

Sheets("SDD File master").Cells(i, 12)

and press shift-F9. What do you get?

then select

Sheets("SDD File master").Cells(i, 12) - 2 / 24

and press shift-F9. What do you get?
0
 
Wilder1626Author Commented:
Like this, it's working;

.Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 12), "hhmm")

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Could you try my previous comment?
0
 
Wilder1626Author Commented:
wow, i did not know about this shift thing.

Sheets("SDD File master").Cells(i, 12) = Value: out context

Sheets("SDD File master").Cells(i, 12) - 2 / 24 same thing
0
 
Saqib Husain, SyedEngineerCommented:
When you do this do you see the line highlighted yellow?
0
 
Wilder1626Author Commented:
No, at all.

Do i need to click on add the spy?
0
 
Wilder1626Author Commented:
The time in the cell is this format: ex: 12:00
0
 
Saqib Husain, SyedEngineerCommented:
Would you be able to upload part of your file with only those cells populated which matter? (unless it is confidential because everyone else can see it here)
0
 
Wilder1626Author Commented:
ok, let me work on this and i will send a copy.

Thanks again
0
 
Wilder1626Author Commented:
Ok, this should be a good example of my excel macro sheet.

Thanks again for your help
Classeur1.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Use this formula

.Cells(Derlign, 10) = Format(CVDate(Sheets("SDD File master").Cells(i, 12)) - 2 / 24, "yyyymmddhhmm")
0
 
Wilder1626Author Commented:
I think that we are almost there.

The format in sheet "SDD order file" should be (YYYYMMDDHHMM)

But what i see is 189912301130

I'm in year: 1899  Month: 12  Day: 30 time: 1130

So date is not good
0
 
Saqib Husain, SyedEngineerCommented:
oops

.Cells(Derlign, 10) = Format(CVDate(Sheets("SDD File master").Cells(i, 11)) + CVDate(Sheets("SDD File master").Cells(i, 12)) - 2 / 24, "yyyymmddhhmm")
0
 
Wilder1626Author Commented:
where are again closer to the final result

If i have this:
 Delv Date      Delv Tm
04/12/2011      12:30

Then, i should have the final convert of: 201104121030

But what i have is: 201112041030

The month and the date is not good

0
 
Saqib Husain, SyedEngineerCommented:
Then lets go back to what you had originally

 .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 11), "yyyymmdd") & Format(cvdate(Sheets("SDD File master").Cells(i, 12))-2/12, "hhmm")
0
 
Wilder1626Author Commented:
Oh yes

Thanks again for your help.

This is very good.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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