We help IT Professionals succeed at work.

date / time format issue

Wilder1626
Wilder1626 asked
on
Medium Priority
471 Views
Last Modified: 2012-05-11
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








Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
.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")
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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")
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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")

CERTIFIED EXPERT

Author

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?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
does it work otherwise?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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?
CERTIFIED EXPERT

Author

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

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Could you try my previous comment?
CERTIFIED EXPERT

Author

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
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
When you do this do you see the line highlighted yellow?
CERTIFIED EXPERT

Author

Commented:
No, at all.

Do i need to click on add the spy?
CERTIFIED EXPERT

Author

Commented:
The time in the cell is this format: ex: 12:00
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
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)
CERTIFIED EXPERT

Author

Commented:
ok, let me work on this and i will send a copy.

Thanks again
CERTIFIED EXPERT

Author

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

Thanks again for your help
Classeur1.xlsm
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Use this formula

.Cells(Derlign, 10) = Format(CVDate(Sheets("SDD File master").Cells(i, 12)) - 2 / 24, "yyyymmddhhmm")
CERTIFIED EXPERT

Author

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
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
oops

.Cells(Derlign, 10) = Format(CVDate(Sheets("SDD File master").Cells(i, 11)) + CVDate(Sheets("SDD File master").Cells(i, 12)) - 2 / 24, "yyyymmddhhmm")
CERTIFIED EXPERT

Author

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

Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Oh yes

Thanks again for your help.

This is very good.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.