I posted this question befo
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the duration. I
am required to calculate the duration like the example above i.e. instead of
showing it as 0:45 min (00:00 (12:00 AM) €“ 00:45 (12:45 AM) = 0:45 minutes,
my client requires it to show as three quarters of an hour in decimal format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

=(endTime - startTime)*24
format as general

Mangesh




"Al" wrote in message
...
I posted this question befo
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the duration.
I
am required to calculate the duration like the example above i.e. instead
of
showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
my client requires it to show as three quarters of an hour in decimal
format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

Sorry, hadn't read the compete mail. Use:

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

B1 is end time
A1 start time

Mangesh



"Al" wrote in message
...
I posted this question befo
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the duration.
I
am required to calculate the duration like the example above i.e. instead
of
showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
my client requires it to show as three quarters of an hour in decimal
format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

I used it and got the same answer "-20.5". did you try it?
I am subtracting 21:30 (start time) from 1:00 (End Time), it should be 3.5.
This is the answer I am looking for. I have my cell formated as general.
thanks

"Mangesh Yadav" wrote:

Sorry, hadn't read the compete mail. Use:

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

B1 is end time
A1 start time

Mangesh



"Al" wrote in message
...
I posted this question befo
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the duration.
I
am required to calculate the duration like the example above i.e. instead
of
showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
my client requires it to show as three quarters of an hour in decimal
format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then
the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

I am getting 3.5
Please check the values B1 is end time and A1 start time.

Mangesh



"Al" wrote in message
...
I used it and got the same answer "-20.5". did you try it?
I am subtracting 21:30 (start time) from 1:00 (End Time), it should be
3.5.
This is the answer I am looking for. I have my cell formated as general.
thanks

"Mangesh Yadav" wrote:

Sorry, hadn't read the compete mail. Use:

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

B1 is end time
A1 start time

Mangesh



"Al" wrote in message
...
I posted this question befo
I need to calculate in a cell the time duration when the user inters
start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the
duration.
I
am required to calculate the duration like the example above i.e.
instead
of
showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
my client requires it to show as three quarters of an hour in decimal
format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45
then
the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

Sorry, It works. thank you very much, you have been a great help.
Al

"Mangesh Yadav" wrote:

I am getting 3.5
Please check the values B1 is end time and A1 start time.

Mangesh



"Al" wrote in message
...
I used it and got the same answer "-20.5". did you try it?
I am subtracting 21:30 (start time) from 1:00 (End Time), it should be
3.5.
This is the answer I am looking for. I have my cell formated as general.
thanks

"Mangesh Yadav" wrote:

Sorry, hadn't read the compete mail. Use:

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

B1 is end time
A1 start time

Mangesh



"Al" wrote in message
...
I posted this question befo
I need to calculate in a cell the time duration when the user inters
start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the
duration.
I
am required to calculate the duration like the example above i.e.
instead
of
showing it as 0:45 min (00:00 (12:00 AM) - 00:45 (12:45 AM) = 0:45
minutes,
my client requires it to show as three quarters of an hour in decimal
format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45
then
the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

Another option would be to include both the date and time in the cell. This
would make it a little more robust--if you crossed two midnights.

Al wrote:

I posted this question befo
I need to calculate in a cell the time duration when the user inters start
and end times in the following fashion:

Start End Duration
00:00 00:45 0.75
00:45 01:30 0.75

Start and End times must be in 24hr format. My problem is in the duration. I
am required to calculate the duration like the example above i.e. instead of
showing it as 0:45 min (00:00 (12:00 AM) €“ 00:45 (12:45 AM) = 0:45 minutes,
my client requires it to show as three quarters of an hour in decimal format
(0.75). how can I do that? I need the user to enter 00:00 and 00:45 then the
form will calculate the 0.75.

and got the following solution:
(B2-B1)*24

The Problem:
this works fine with most but not in the following case:
Start End
21:30 1:00
The answer I am getting is -20.5? it should be 3.5
any Idea.
Thanks
Al

--

Dave Peterson

slightly shorter option

=(B1-A1)*24+IF((B1-A1)*24<0,24,0)

=(B1-A1+(A1B1))*24

hth RES