Excel problems

Closed
Report
Posts
1
Registration date
Tuesday December 8, 2009
Status
Member
Last seen
December 8, 2009
-
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
-
Hello,

I have some really tricky questions, i hope someone will be able to help me please.

First of all i have two time values, eg. 19:00 and 21:00 im wanting to subtract this times and finish up with a value of 120 (minutes) so A1 = 19:00 B1 = 21:00 and C1 = (A2-A1) What is the formula i can replace this with to end up with 120 (minutes) I will have a long list of times that will need to be subtracted from each other, leaving the minutes in between.

Secondly, if the real time is (19:00 to 21:00) and A1 = 19:00 and B1 = 21:00 i would like the D1 to have a green circle in it, show that the this time is active, and red circle at all other times. What formula would have to be in D1. Would the formula =NOW() have to be in another cell? If so please refer to this as E1

Thank you for all your time, and i hope this will be not too tricky for someone.

Much appreciated,

Matt

1 reply

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
Problem no 1:
set the cells with times in formated as time
set the cell that you require the (in this case 120) result in as number no decimal places.
Use this formula
ie - =Sum(B1-A1)*1440.
this will give you the time in minutes between the two times.

I'll have to have a think about the 2nd problem, unless anybody out there has any suggestions.