Automated Counter + 24 hour Time Difference.

all4excel · 01-25-2008, 02:52 AM

Dynamic Dropdown + Automated Counter + Time Difference.
I would need help on the following :
Dynamic Dropdown + Automated Counter + Time Difference.

I have attached the file in which I have mentioned the requirements.

1. Dynamic Dropdown :

I have a column mentioning vertical, based on the choice selected in this dropdown in the adjoiing column should display only those values in that drop-down.

2. Automated Counter :

There would be several activities done in a day for a single date, the date is mentioned the column only once so the counter needs to keep a track of the row numbers before it encounters the new date.

3. Time Difference :

The time should work for a 24 hour clock...and not only for 8 hours...(regular shift) The Start time cannot always be less than the End time so if goes in the next day then there arises an error.

all4excel · 01-25-2008, 03:10 AM

Please go thru the attachment..

Thanks in anticipation..

Someone help me on the counter partion atleast...

all4excel · 01-26-2008, 05:37 AM

Automated Counter + 24 hour Time Difference.

Automated Counter

I have this tracker where I note down different activities in a day. There can be more than one activity in a particular day or DATE..I will be entering the date in the column A only once and then Enter the different activities performed for that day.Then I will enter the next date again in the column A.I want a counter which counts the number of activities btween two dates and then again start all over again for the next date....

Time Difference

I want the differenc in time however I want it to work for a 24 hour format..
I would aprreciate if could write this in a decimal format rather than putting ":" if that’s possible .For Ex:- Instead of writing 14:30 , I would just write 14.30 but the code should consider it a s time format in the background and give the difference.

Please provide a solution for the same...Thanks in anticipation..

all4excel · 01-26-2008, 06:40 AM

Have figured Automatic counter but for Time still require help...

Code :-
IF(AND(ISBLANK(A2),ISBLANK(C2)),"",IF(NOT(ISBLANK(A2)),1,IF(ISBLANK(A2),B1+1,1)))

Please someone help me for the Time Difference...
Things expected are :

1. If I can write 14.30 instead of 14:30 in the Start/end Time and get the difference.

2. If I am working at 19:00 hours and finish my work the next day at 4:00AM early morning ..

Then it would give me an answer of 19:00 to 24:00 = 5 hours + 4 hoyrs = total hours worked 9 hours.

Please find the attachment below for the Automated Counter, others can find this useful..

**VBA Noob** · 01-26-2008, 08:03 AM

Try

=(E2-D2+(D2>E2))

and try the attached for quick time entry

http://www.cpearson.com/excel/DateTimeEntry.htm

VBA Noob

all4excel · 01-27-2008, 01:56 PM

Dear Noob,

It worked wel but cn u plz explain what happens (G2-F2+(F2>G2))
I didnt understand F2 > G2.... I mean what gets added..

**VBA Noob** · 01-27-2008, 02:15 PM

(F2>G2)

returns true or false

Excel reads true as 1 and false as 0

So if

G2-F2

is neg (true) the above adds a 1 i.e 24 hrs so F2 is always > than G2

VBA Noob

all4excel · 01-27-2008, 04:04 PM

However, I also had expressed one more thing in my question..

Is it possible to have the times expressed in G2 and F2 as numbers...
Like 14.0 or 4.0 ...

I am basically wanting to type number which should be considered as time for Excel.

The calculation shud remain the same however the values for ease wud be typed as numbers..

Like 14:00 wud be typed as 14 or 14.0...

Is that possible..I remebemr of going thru a link in the forum about time calculated when it was expressed as numbers bur dont have the link anymore..