Time/hour question

[bowelripper]

Hello!

I have a couple of questions concerning the use of time/hours in Excel 2007.

Firstly, it seems I have to use a colon for the time format. In other words 7 in the morning must be entered as 07:00 in a cell, but in Norway where I live the correct format is 07.00 (alternatively 0700). If I try the latter it just gets messed up. Is it possible to work with the latter format? How? (By the way, we use the 24-hour system here.)

Secondly, I have a formula where I need to state that if a certain hour in a cell is less than /earlier than 07:00 it should calculate a value. Example where let's say B8 equals 06:00: =IF(B8<07:00;(07:00-B8)*24;0). The problem is that Excel won't accept 07:00 as a value in the formula bar, so how can I reformulate this?

Thanks,
BR

 

[drsarao]

Welcome to MrExcel.

For your second query use TIMEVALUE()
eg. =IF(B8< TIMEVALUE("07:00");(TIMEVALUE("07:00")-B8)*24;0)

Your first query concerns international settings. I think.

 

[shift-del]

Hello

Two more ways to handle this:

<table valign="middle" colspan="3" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28"><col width="60"><col width="60"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="3" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">B</td><td align="middle">C</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td align="right">06:00</td><td align="right">1</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td align="right">06:00</td><td align="right">1</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>C8</td><td>=IF(B8<7/24,(7/24-B8)*24,0)</td></tr><tr><td>C9</td><td>=IF(B9<TIME(7,0,0),(TIME(7,0,0)-B9)*24,0)</td></tr></tbody></table>

 

[ge0rge]

Oh my, what a screen name, welcome to the board.

 

[bowelripper]

Alright, thanks a lot for the fast answers! They solved my problems! I'm still wondering about my first question, though, but it's not really a big deal.

 

[Scott Huish]

You can use VBA to convert it, by the latter method do you mean the one with the decimal point or the one without?

 

[bowelripper]

You can use VBA to convert it, by the latter method do you mean the one with the decimal point or the one without?

I would prefer the one with the decimal point (actually, in Norwegian the comma is the decimal point and the period is the time separator). What is VBA?

 

[Scott Huish]

It's a programming language that comes with Excel, it is based on Visual Basic and is called Visual Basic for Applications.

 

[bowelripper]

It's a programming language that comes with Excel, it is based on Visual Basic and is called Visual Basic for Applications.

So, what do I actually have to do to convert?

 

[jeffreybrown]

You do not have to convert anything VBA is already part of Excel. Open up Excel and select Alt + F11 and this will open the VBA editor (VBE) and this is where you enter code.