Problem with formula using now()

[rhombus4]

I have a number (a2) start time(b2) and a stop time(c2) other time (d2) and a net time (e2) I also have a fixed value in Z1 which is 1:00:00

net time is just c2-b2-d2 and then to calculate how long it takes for each 1 (f2) = net time/number

g2 is per hour (=Z$1/F2)

SO for example

<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=342 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" span=2 width=39><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=50>number</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>start</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=37>stop</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>other</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>net</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=57>Every</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=56>per hour</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.33333333333333331">08:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.5">12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="4.1666666666666664E-2">01:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.125" x:fmla="=C2-B2-D2">03:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 align=right x:num="1.041666666666667E-2" x:fmla="=E2/A2">0:15:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 align=right x:num>4.0000</TD></TR></TBODY></TABLE>

However for the stop time instead of manually entering a time i want to use the =now() and if it was 12:00 as above the net figure works ok but the Every shows as 20:15:02 and per hour is 1.2E-05

B, C, D and E are formatted as hh:mm F is formatted as h:mm:ss amd G is formatted as General

PS when looking at the custom formats what is the difference between [h]:mm:ss and h:mm:ss

 

[wigi]

If you enter 20:15:02, or a formula does this, what is the REAL contents of the cell? You can verify this by looking at the Cell properties > Number > General.

 

[rhombus4]

If you enter 20:15:02, or a formula does this, what is the REAL contents of the cell? You can verify this by looking at the Cell properties > Number > General.

THe 20:15:02 in column F should = 0:15:00 ie 1 every 15 mins, done 12 in a net of 3 hours.

If i entered 20:15:02 in F the per hour changes to 0.04938 F is formatted as h:mm:ss

F should = e3/a3 which is the net time/number so 3:00/12 which should be 0:15:00

 

[wigi]

So, you enter data in column B.

Instead of 12:00, you enter 20:15:02.

Obviously, the other columns will change too because they depend on column B.

What is still going wrong, given this?

 

[rhombus4]

So, you enter data in column B.

Instead of 12:00, you enter 20:15:02.

Obviously, the other columns will change too because they depend on column B.

What is still going wrong, given this?

I enter the start time in Column B i.e. 8:00
Column C is the stop time when I manually enter a stop time the results in F and G work ok. However In the stop time(column C) I want to use =now().

That when it throws the results out in F and G
F should be 0:15:00 and G 4 because the net time in the example above was 3 hours. column F formula (E3/A3) (3:00/12 =0:15:00) and G formula Z$1/F2 (1:00:00/0:15:00 =4)

this is what is should be when I manually enter 12:00 in the stop column (f3)
<TABLE style="WIDTH: 272pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=362 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" span=2 width=39><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=50>number</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>start</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=57>stop</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>other</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>net</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=57>Every</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=56>per hour</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.33333333333333331">08:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.5">12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="4.1666666666666664E-2">01:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.125" x:fmla="=C2-B2-D2">03:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 align=right x:num="1.041666666666667E-2" x:fmla="=E2/A2">0:15:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right x:num>4</TD></TR></TBODY></TABLE>

and this is what i get when i enter =now() in F3

<TABLE style="WIDTH: 230pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=306 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" span=2 width=39><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=50>number</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>start</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=57>stop</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>other</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=39>net</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=57>Every</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=17 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.33333333333333331">08:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.5">12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="4.1666666666666664E-2">01:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 align=right x:num="0.125" x:fmla="=C2-B2-D2">03:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl26 align=right x:num="0.84377314814814808">20:15:02</TD></TR></TBODY></TABLE>

 

[wigi]

This is because NOW also has the day of today.

I would advise to use the shortcut Ctrl-:
On my keyboard and settings (do not know if it's the same for you), it gives you the current time. Not as a formula, but as a fixed value. For instance, 1:19, and the number in Excel will be 0.05486 and so on.

 

[rhombus4]

This is because NOW also has the day of today.

I would advise to use the shortcut Ctrl-:
On my keyboard and settings (do not know if it's the same for you), it gives you the current time. Not as a formula, but as a fixed value. For instance, 1:19, and the number in Excel will be 0.05486 and so on.

So isnt it possible to have the result which =now() gives subtract a time then divide that time by 4

say its 8am now and I enter 7am in B2 and =now() in C2.
E2 is 8am - 7am so 1:00 so why cant it divide 1:00 by 4 to give 0:15:00, the reason im using this is because a macro is run and the result is transferred to sheet1 so the user knows what the current stat is.

If not possible couldnt i enter =now() is say Z2 and in c2 it would enter a paste special of whatever Z2 is?????

 

[wigi]

You can use an extra column.

Use

=NOW()

in that column (say X2), and in C2:

=X2-INT(X2)

 

[rhombus4]

You can use an extra column.

Use

=NOW()

in that column (say X2), and in C2:

=X2-INT(X2)

Thanks Wigi. seems to work ok although didnt understand (This is because NOW also has the day of today) why the today made a difference if it was on the same day and formated as h:mm

But it works which is the main thing

 

[wigi]

By the nature of the NOW() function, it gives you the number of days since Jan 1, 1900. So today 40,763

The decimals are the number of hours: 0.5 is 12 AM, 0.75 is 6 PM, and so on.

I you only enter the decimal part, you have a difference of about 40,763 !!!