how do i calculate the difference in time?
 

Hi guys... I have a similiar query.... plzzzz can someone help me!

I'm constructing a spreadsheet to work out the difference in time and date;
A1 A2 A3
07/11/04 11:00 04/11/04 10:00 3:1:00 (d:h:mm)
but now i'm trying to work out a fomula that if it goes over one day the
formula would work out true/false... i've tried doing the following but it
doesn't work;

=IF(A3=1,"Failed","Met") but it doesn't work...... doesn't seem to
register the day

Thank you

If I'm working in the 1900 date system and my differences are less than
31 days, your formula works for me.

The formula I used in A3 was =A1-A2, formatted as "d:h:mm"

In the 1904 date system however, the "d" will be one day off, since "d"
refers to "day of the month". Since the zeroth day is 1/1/1904, a value
of 0.5 formatted as "d:h:mm" returns 1:12:00, not 0:12:00.


In article ,
"Roze" wrote:

Hi guys... I have a similiar query.... plzzzz can someone help me!

I'm constructing a spreadsheet to work out the difference in time and date;
A1 A2 A3
07/11/04 11:00 04/11/04 10:00 3:1:00 (d:h:mm)
but now i'm trying to work out a fomula that if it goes over one day the
formula would work out true/false... i've tried doing the following but it
doesn't work;

=IF(A3=1,"Failed","Met") but it doesn't work...... doesn't seem to
register the day

Thank you

Hi

=(A3=1)
must return TRUE for your example - otherwise you have made an error
somewhere.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"Roze" wrote in message
...
Hi guys... I have a similiar query.... plzzzz can someone help me!

I'm constructing a spreadsheet to work out the difference in time and
date;
A1 A2 A3
07/11/04 11:00 04/11/04 10:00 3:1:00 (d:h:mm)
but now i'm trying to work out a fomula that if it goes over one day the
formula would work out true/false... i've tried doing the following but it
doesn't work;

=IF(A3=1,"Failed","Met") but it doesn't work...... doesn't seem to
register the day

Thank you

Should have said "formulas". The formula

=IF(A31,"Failed","Met")

worked fine, even if the display in A3 didn't in the 1904 date system.

In article ,
JE McGimpsey wrote:

If I'm working in the 1900 date system and my differences are less than
31 days, your formula works for me.

I've tried that, it doesn't seem to work

I'm trying to work out a spreadsheet that works out the differenct date and
time difference... e.g:

A1 A2 A3 A4
Recieved Date/Time Replied Date/Time Total Met/Failed
04/11/04 10:00 05/11/04 11:00 1:1:00

I need a Met/Failed column(Met= 1) (Failed=0) which automatically works it
out if it's taken more than 1 day to reply to the email, then it's 0 (Failed)

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

Please can you help.....

Thank you!!!


"JE McGimpsey" wrote:

Should have said "formulas". The formula

=IF(A31,"Failed","Met")

worked fine, even if the display in A3 didn't in the 1904 date system.

In article ,
JE McGimpsey wrote:

If I'm working in the 1900 date system and my differences are less than
31 days, your formula works for me.

What does "it doesn't work" mean to you?

Including your values in quotes makes them Text, but they should still
show up. Remove the quotes for numeric values, or use XL's implicit
conversion of TRUE/FALSE to 1/0 in math formulas:

= --(A3 < 1)



In article ,
"Roze" wrote:

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

1) 04/11/04 10:00 04/11/04 17:00 0:7:00 TRUE
2) 04/11/04 08:00 04/11/04 08:00 0:0:00 TRUE
3) 01/11/04 11:00 02/11/04 13:00 1:2:00 TRUE

I'm sorry for being such a pain.........

If you take a look at rows 1 and 2 they are correct but the formula you gave
me doesn't work for 3... as it should be false because it's taken 1d 2hr 00m
to reply to email......

is their another way to work this spreadsheet, perhaps work only in hours
(aslong as it includes hours from both days and then do a formula for <24
hrs???

Thank you for all your help so far... very much appreciated!!!!

"JE McGimpsey" wrote:

What does "it doesn't work" mean to you?

Including your values in quotes makes them Text, but they should still
show up. Remove the quotes for numeric values, or use XL's implicit
conversion of TRUE/FALSE to 1/0 in math formulas:

= --(A3 < 1)



In article ,
"Roze" wrote:

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

Your previous examples had values in a column, (e.g., A1, A2, A3), so
that's what the proposed solutions were based on, but your latest
example shows the values in rows.

Did you change the reference appropriately? e.g.,

A3: 01/11/04 11:00
B3: 02/11/04 13:00
C3: B3-A3 === 1:2:00
D3: IF(C3=1,0,1) === 0

????



In article ,
"Roze" wrote:

1) 04/11/04 10:00 04/11/04 17:00 0:7:00 TRUE
2) 04/11/04 08:00 04/11/04 08:00 0:0:00 TRUE
3) 01/11/04 11:00 02/11/04 13:00 1:2:00 TRUE

I'm sorry for being such a pain.........

If you take a look at rows 1 and 2 they are correct but the formula you gave
me doesn't work for 3... as it should be false because it's taken 1d 2hr 00m
to reply to email......

is their another way to work this spreadsheet, perhaps work only in hours
(aslong as it includes hours from both days and then do a formula for <24
hrs???

Thank you for all your help so far... very much appreciated!!!!

"JE McGimpsey" wrote:

What does "it doesn't work" mean to you?

Including your values in quotes makes them Text, but they should still
show up. Remove the quotes for numeric values, or use XL's implicit
conversion of TRUE/FALSE to 1/0 in math formulas:

= --(A3 < 1)



In article ,
"Roze" wrote:

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

This formula still doesn't work, maybe it's me not explaning myself
properley......
What i need to do is
d:h:mm
e.g: 1:2:00 should = 0
0:2:00 should = 1
because if we complete the email within the 24 hrs (1day) then it should be
0, otherwise it should be 1, I've been trying various formula's to help me
but nothing calculates the D3 column correctly.......

D3: IF(C3=1,0,1) === 0



"JE McGimpsey" wrote:

Your previous examples had values in a column, (e.g., A1, A2, A3), so
that's what the proposed solutions were based on, but your latest
example shows the values in rows.

Did you change the reference appropriately? e.g.,

A3: 01/11/04 11:00
B3: 02/11/04 13:00
C3: B3-A3 === 1:2:00
D3: IF(C3=1,0,1) === 0

????



In article ,
"Roze" wrote:

1) 04/11/04 10:00 04/11/04 17:00 0:7:00 TRUE
2) 04/11/04 08:00 04/11/04 08:00 0:0:00 TRUE
3) 01/11/04 11:00 02/11/04 13:00 1:2:00 TRUE

I'm sorry for being such a pain.........

If you take a look at rows 1 and 2 they are correct but the formula you gave
me doesn't work for 3... as it should be false because it's taken 1d 2hr 00m
to reply to email......

is their another way to work this spreadsheet, perhaps work only in hours
(aslong as it includes hours from both days and then do a formula for <24
hrs???

Thank you for all your help so far... very much appreciated!!!!

"JE McGimpsey" wrote:

What does "it doesn't work" mean to you?

Including your values in quotes makes them Text, but they should still
show up. Remove the quotes for numeric values, or use XL's implicit
conversion of TRUE/FALSE to 1/0 in math formulas:

= --(A3 < 1)



In article ,
"Roze" wrote:

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

Is there another way of working this out in hours???

E.g

01/11/04 11:00 02/11/04 11:00 = 24 hrs
01/11/04 11:00 02/11/04 10:00 = 23 hrs

and work out a formula that =24hrs = 0, <24 hrs = 1

Maybe this might work better?????????

"Roze" wrote:

This formula still doesn't work, maybe it's me not explaning myself
properley......
What i need to do is
d:h:mm
e.g: 1:2:00 should = 0
0:2:00 should = 1
because if we complete the email within the 24 hrs (1day) then it should be
0, otherwise it should be 1, I've been trying various formula's to help me
but nothing calculates the D3 column correctly.......

D3: IF(C3=1,0,1) === 0



"JE McGimpsey" wrote:

Your previous examples had values in a column, (e.g., A1, A2, A3), so
that's what the proposed solutions were based on, but your latest
example shows the values in rows.

Did you change the reference appropriately? e.g.,

A3: 01/11/04 11:00
B3: 02/11/04 13:00
C3: B3-A3 === 1:2:00
D3: IF(C3=1,0,1) === 0

????



In article ,
"Roze" wrote:

1) 04/11/04 10:00 04/11/04 17:00 0:7:00 TRUE
2) 04/11/04 08:00 04/11/04 08:00 0:0:00 TRUE
3) 01/11/04 11:00 02/11/04 13:00 1:2:00 TRUE

I'm sorry for being such a pain.........

If you take a look at rows 1 and 2 they are correct but the formula you gave
me doesn't work for 3... as it should be false because it's taken 1d 2hr 00m
to reply to email......

is their another way to work this spreadsheet, perhaps work only in hours
(aslong as it includes hours from both days and then do a formula for <24
hrs???

Thank you for all your help so far... very much appreciated!!!!

"JE McGimpsey" wrote:

What does "it doesn't work" mean to you?

Including your values in quotes makes them Text, but they should still
show up. Remove the quotes for numeric values, or use XL's implicit
conversion of TRUE/FALSE to 1/0 in math formulas:

= --(A3 < 1)



In article ,
"Roze" wrote:

I tried doing the follwing formula but it doesn't work;
=IF(A3=1,"0","1")

I don't know if you're explaining properly or not. I get exactly what
you say you want, so I'm afraid I can't help you much.

I put an example workbook up on my ftp site. It gives the correct
results for me:

ftp://ftp.mcgimpsey.com/excel/roze_demo.xls


In article ,
"Roze" wrote:

This formula still doesn't work, maybe it's me not explaning myself
properley......
What i need to do is
d:h:mm
e.g: 1:2:00 should = 0
0:2:00 should = 1
because if we complete the email within the 24 hrs (1day) then it should be
0, otherwise it should be 1, I've been trying various formula's to help me
but nothing calculates the D3 column correctly.......

D3: IF(C3=1,0,1) === 0

Thank you so much... it works!!

Just another little query, if i was doing the same spreadsheet but the reply
email time limit was 2 hrs, how would i do that?? 2hr = 0, =<2hr= 1

And you know when you apply a formula to the spreadsheet, how can i have the
column filled down with the formula, so i don't keep inserting it one by one??

Thank you again for all your help...... you've helped me alot!!!
Very much appreciated JE McGimpsey ...

Just need a little more help with the above two.....

"JE McGimpsey" wrote:

I don't know if you're explaining properly or not. I get exactly what
you say you want, so I'm afraid I can't help you much.

I put an example workbook up on my ftp site. It gives the correct
results for me:

ftp://ftp.mcgimpsey.com/excel/roze_demo.xls


In article ,
"Roze" wrote:

This formula still doesn't work, maybe it's me not explaning myself
properley......
What i need to do is
d:h:mm
e.g: 1:2:00 should = 0
0:2:00 should = 1
because if we complete the email within the 24 hrs (1day) then it should be
0, otherwise it should be 1, I've been trying various formula's to help me
but nothing calculates the D3 column correctly.......

D3: IF(C3=1,0,1) === 0

1) XL stores times as fractional days. You can use the built-in TIME
function:

=IF(C1 < TIME(0,2,0), 1, 0)

or just divide the number of hours by 24:

=IF(C1 < 2/24, 1, 0)

2) Tools/Options/Edit check the Extend list formats as formulas
checkbox (but read Help - there are limitations). Or you can do
something like:

=IF(C1="", "", IF(C1 < 2/24, 1, 0))



In article ,
"Roze" wrote:

Just another little query, if i was doing the same spreadsheet but the reply
email time limit was 2 hrs, how would i do that?? 2hr = 0, =<2hr= 1

And you know when you apply a formula to the spreadsheet, how can i have the
column filled down with the formula, so i don't keep inserting it one by one??

I would just like to thank you for all your time and help in answering my
queries.. This is the first time I've ever I've used it and found it very
helpful...... Thank you again!!!!

"JE McGimpsey" wrote:

1) XL stores times as fractional days. You can use the built-in TIME
function:

=IF(C1 < TIME(0,2,0), 1, 0)

or just divide the number of hours by 24:

=IF(C1 < 2/24, 1, 0)

2) Tools/Options/Edit check the Extend list formats as formulas
checkbox (but read Help - there are limitations). Or you can do
something like:

=IF(C1="", "", IF(C1 < 2/24, 1, 0))



In article ,
"Roze" wrote:

Just another little query, if i was doing the same spreadsheet but the reply
email time limit was 2 hrs, how would i do that?? 2hr = 0, =<2hr= 1

And you know when you apply a formula to the spreadsheet, how can i have the
column filled down with the formula, so i don't keep inserting it one by one??

how do i calculate the difference in time?
 

hi. My question is also similar. We work shifts round-the-clock here,
and I need to keep track of hours worked per shift (some are 12, 10, 8, ...).
Through experimentation, I have managed this:

=IF(C5D5,(ABS(C5))-(ABS(D5)),D5-C5). with C5 as the start time, and D5 the
end time.

It works fine, except when some one starts at 10PM, and gets off at
8AM. It shows 14 hours worked when it should be 10.
Also, I would like to enter the times as 2200 instead of 22:00. Is
that possible?
When I do it with out the colon, I usually get 12AM and a strange date.


"Roze" wrote:

Hi guys... I have a similiar query.... plzzzz can someone help me!

I'm constructing a spreadsheet to work out the difference in time and date;
A1 A2 A3
07/11/04 11:00 04/11/04 10:00 3:1:00 (d:h:mm)
but now i'm trying to work out a fomula that if it goes over one day the
formula would work out true/false... i've tried doing the following but it
doesn't work;

=IF(A3=1,"Failed","Met") but it doesn't work...... doesn't seem to
register the day

Thank you

how do i calculate the difference in time?
 

Thank you - this post really helped me

"JE McGimpsey" wrote:

1) XL stores times as fractional days. You can use the built-in TIME
function:

=IF(C1 < TIME(0,2,0), 1, 0)

or just divide the number of hours by 24:

=IF(C1 < 2/24, 1, 0)

2) Tools/Options/Edit check the Extend list formats as formulas
checkbox (but read Help - there are limitations). Or you can do
something like:

=IF(C1="", "", IF(C1 < 2/24, 1, 0))



In article ,
"Roze" wrote:

Just another little query, if i was doing the same spreadsheet but the reply
email time limit was 2 hrs, how would i do that?? 2hr = 0, =<2hr= 1

And you know when you apply a formula to the spreadsheet, how can i have the
column filled down with the formula, so i don't keep inserting it one by one??