Previous Topic Next Topic

Excel problems

Closed
splnty Posts 1 Registration date Tuesday December 8, 2009 Status Member Last seen December 8, 2009 - Dec 8, 2009 at 06:14 PM
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 - Dec 11, 2009 at 02:04 PM
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
Related:

1 response

Answer 1 / 1
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Dec 11, 2009 at 02:04 PM
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.
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses