0

Is is possible to do calulation on Sheet2 collecting numbers from sheet1? The reason im asking, I have a *.csv who i import to a excel document. (sheet1) When i do the calculation in sheet 2 i get "#value!" If i do the calculation=((Sheet1!G1+Sheet1!H1)-(Sheet1!C1+Sheet1!D1))*24

Example Sheet1(data from csv)

A1        B1       C1         D1    E1         F1    G1         H1
546545656 Stianbla 31.08.2015 17:36 31.08.2015 17:36 31.08.2015 17:36

Example Sheet2

A1          B1        C1         D1         E1         F1         G1         H1    
=SHEET1!A1 =SHEET1!B1 =SHEET1!C3 =SHEET1!D1 =SHEET1!E1 =SHEET1!F1 =SHEET1!G1 =SHEET1!H1
Improve this question
4
  • (Sheet1!G1+Sheet1!H1) and (Sheet1!C1+Sheet1!D1) probably give you an error because you're trying to add a time (H1) to a date (G1), which are in two different formats. As @kyle points out below, 31.08.2015 may not even count as a "date" in any format. :) If you add =((G1+H1)-(C1+D1))*24 to a cell on Sheet1, does it display what you expect, or do you still get a #VALUE! error?
    – Ƭᴇcʜιᴇ007
    Aug 31, 2015 at 18:53
  • @Ƭᴇcʜιᴇ007 Dates and times are stored in exactly the same manner in Excel. The problem is that Excel does not recognize values like "31.08.2015" as a date.
    – Kyle
    Aug 31, 2015 at 18:55
  • @Kyle that's a good point. :)
    – Ƭᴇcʜιᴇ007
    Aug 31, 2015 at 18:57
  • If I do the clean calc on the same sheet it works. but I can see the format change.
    – Stian Størstfeskar Andersen
    Aug 31, 2015 at 21:39

1 Answer 1

Reset to default
0

Your problem is that Excel does not recognize values like 31.08.2015 as a date. You can force the dates into the correct format like this:

=DATEVALUE(MID(Sheet1!G1,4,2)&"/"&LEFT(Sheet1!G1,2)&"/"&RIGHT(Sheet1!G1,4))

Making your entire formula look like this:

=((DATEVALUE(MID(Sheet1!G1,4,2)&"/"&LEFT(Sheet1!G1,2)&"/"&RIGHT(Sheet1!G1,4))+Sheet1!H1)-(DATEVALUE(MID(Sheet1!C1,4,2)&"/"&LEFT(Sheet1!C1,2)&"/"&RIGHT(Sheet1!C1,4))+Sheet1!D1))*24
Improve this answer
2
  • Is it a more recognized way to save the date? So the code can be more simple? I can edit the input data in csv example dd/mm/yyyy ?
    – Stian Størstfeskar Andersen
    Aug 31, 2015 at 21:51
  • @StianStørstfeskarAndersen Try mm/dd/yyyy. I'm actually not sure if it differs by region.
    – Kyle
    Sep 1, 2015 at 2:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .