i am using the attached sheet to export data from excel to outlook calendar from last 2-3 months. Now suddenly it stopped working for me (I didn't amend the code)
All the data i have filled in is in the correct format but the code doesn't export the data after ROW 72.
Dunno what the hell is wrong with this code .
Please help me out.
Thanks in advance.
Last edited by dollar; 08-11-2009 at 01:10 PM.
the code looks ok per se but some of your end times precede the start times ... to illustrate:
F2: =SUM(A2,C2)>SUM(B2,D2)
copied down for all entries
where TRUE you are likely to experience "issues"... why TRUE - well some of the time values include the Date portion too .. eg C78 is 2/1/1900 08:00 which equates to 56 hours (2 days and 8 hours)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
ok but what's the solution ?
... to ensure your start times precede your end times ... and more specifically to ensure your time values are < 24 hours.
why does the time duration should be under 24 hours ?
Kindky re-check this attached file .
Thanks
& one more thing; is there any userform for filling up time values in column C & Column D (like we use calendar form to fill in dates) ?
Thanks
I am not sure if I'm making myself as clear as I had intended.
Look at the following values in the formula bar:
C2: 09:15:00
C3: 02/01/1900 08:00:00
C4: 02/01/1900 09:15:00
Key, in XL dates are integers, time is decimal...ie noon = 0.5, 6am = 0.25 etc ... 24 hours = 1 - make sense ?
So the value in C2 represents just 9 hours & 15 minutes, the value in C3 however as we can see represents 2 days and 8 hours - ie 56 hours.
To demonstrate more graphically, apply a custom format to C2:C4 of: [h]:mm
You will see the following
C2: 09:15
C3: 56:00
C4: 57:15
so... if you add the Start Date and the Start Time for row 3 you will realise that you are in fact adding 56 hours to the Start Date whereas for the end date you're adding only 9 hours and 15 minutes (D3) to the same date ... therefore the start date is after the end date... this does not make sense.
So going back to my original point - your time values (cols C & D) should always be < 24 hours.
Last edited by DonkeyOte; 08-11-2009 at 11:47 AM. Reason: added C4 initial value also for clarity
ok now i got your point :P ... but could you please enlighten me on; why does this happened? i mean i had filled 8:00 AM (with 5 key strokes) in C3 & had filled up the D3 (9:15 AM) in the same way (task duration 1 hour n 15 minutes). So why does excel showed C3 as 1/2/1990 8:00:00 AM. Moreover i had selected same time format for both of these enteries i.e Time - 1:30 PM ??
I'm afraid I can think of no reason as to why you would get 2/1/1900 08:00 if entering 08:00 into the cell.
The reason the time displayed as it did was simply down to the fact that the format applied did not display cumulative hours, for ex. in a blank cell enter:
54:00
Now apply your Time Format of 1:30 PM - you will see it display as 6:00 AM and you will notice it is displaying the time remainder once the complete days have been accounted for ... if you format the cell to General this will perhaps be a little clearer - ie the result is 2.25, this goes back to my earlier point that days are integers (ie 2 = 2 days) and time is decimal (1/4 of a day - ie 6 hours) ... the Time Format is displaying only the time remainder - ie the 6 hours... using a cumulative hourly format of [h]:mm we can see the 54:00... ie the format disregards looking for days and shows the value only in hour/minute format.
ok
Thanks
hey you didn't comment on my request .. is they a way to fill in time as we use calendar form to fill in dates ?
I'm afraid I don't understand the question... if not directly related to this thread please raise question in new thread.
Originally Posted by DonkeyOte
Hi
I was wondering if I could hire you to do this same thing. I have a excel schedule all the columns have the same field names as outlook calendar and I need 9 fields to update automatically to outlook calender I believe that most of the code is here on this tread already. So what do you think can I hire you to do it?
Did you ever solve this problem?i am using the attached sheet to export data from excel to outlook calendar from last 2-3 months. Now suddenly it stopped working for me (I didn't amend the code)
All the data i have filled in is in the correct format but the code doesn't export the data after ROW 72.
Dunno what the hell is wrong with this code .
Please help me out.
Thanks in advance.
If so I would like to hire you to do the same for me.
I have a excel sheet that using outlook file names and I need to sync the excel sheet to the outlook calendar.
Please get back to me.
Thanks Derrick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks