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

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

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

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