This is for a VBA TextBox (Not Worksheet). I have two text boxes, both are going to have dates. Textbox1 a user will enter a date; example 10/22/08 or 10-22-08 or 10-22-2008 etc, TextBox2 will get that date and add seven days.
View 3 Replies
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
if i write the following function on a checkbox,
View 10 Replies View RelatedI am creating an Add Contact form. There is a checkbox for Individual (if left unticked then contact is a business entity - not an individual).
If Individual = True and If the txtNameFirst control has no value in it Then after the end user tabs away from the txtNameLastRegistered, the form should SetFocus on the txtNameFirst textbox (entry for this field is mandatory only if the contact is an individual - otherwise this field is made invisible)
But it is not working. I tick chkIndividual, I enter a name in txtNameLastRegistered, I tab away, code is called from txtNameLastRegistered_AfterUpdate (which includes the SetFocus code) but the form appears without focus in any of the controls!
My code below:
[Code] ......
I have a UserForm which enables the user to input sales figures for each month for 5 years.
At the bottom of each year is the total box.
At the moment it only sums the 12 months when each month has a figure in it. Sometimes the user only needs to enter, lets say, 4 months figures and the total box doesn't work. It also doesn't sum as data is entered i.e. it only adds them up when all 12 months TextBoxes have a number in them.
Can I literally make it as simple as it would be on a normal worksheet i.e. =SUM(A1:A12) ??
Here is my code that is 'in' each month's TextBox
[Code] .....
End Sub
The total box itself - txtY1Total - doesn't have any code in it.
I am working on a "Salary Payment voucher excel Userform".
How can i do it, if i don't want manually fill in the number of working days into the textbox myself, Can i have the userform to automatically fill in the number of days when i initialize the userform.
We are working at 6 Days weeks.
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
I'm trying to write a macro that filters a table via textbox (criteria), specifying the column to filter through a combobox. I managed to get it to work with every format (date, text, etc.) except with numbers. I'm attaching the file so you can take a look at the code.
If I have a column containing numbers but the cells have text format and I reformat those cells to numbers (using points to separate thousands [I'm from Venezuela, we use dots, not commas]) the results aren't visible unless I modify each cell individually. How can I avoid this?
Lastly, I'm using a macro that I found online that sets invisible shapes on each cell of the header and asigns another macro to these shapes to sort by ascending or descending order in the column over which the shape is put. I made some changes to the macro that actually sorts the values and it works fine, but sometimes I have to resize the shape (on the left side) so that it's further inside the cell or else I'll get an error.
EDIT: It doesn't work with dates either!
EDIT2: I tried copying the table and the codes to a new workbook and now magically it works with numbers, but still not working for dates. Also I'm still having to resize the invisible shape (only in header of the first column ('C')) and the changes in format still aren't visible unless I modify each cell. I think this last issue has something to do with 'SortOneTime' macro or the 'Ordenar' macro because it happens after I run them.
Tabla General (Nueva) (Combobox).xlsm
I have table which looks like this:
Name of School Time1 Time2 LoggedIN
2436 DAVIS JH 11/10/08 8:20 AM 11/10/08 8:30 AM 1 2436 DAVIS JH 11/10/08 8:30 AM 11/10/08 8:40 AM 1 2436 SVM JH 12/11/08 7:00 AM 12/11/08 7:10 AM 1 2436 DAVE JH 12/11/08 7:10 AM 12/11/08 7:20 AM 2 2436 CRAW H 12/11/08 7:20 AM 12/11/08 7:30 AM 2
Total Number of Columns = 4
First Column = School NAme
Second Column = Time 1
Third Column =Time2
Fourth Colum = Logged In
I want a 5th column where i can get the following result:
If time in 3rd column is betweeb 8am and 9 am and time in the 4th column is between 8Am and 9 Amthe thee corresponding row in the 5th coulmn should be 8AM-9AM
If time in 3rd column is betweeb 9am and 10 am and time in the 4th column is between 9AM and 10 Am the thee corresponding row in the 5th coulmn should be 9AM-10AM
Likewise for each hourly time slot of the day
The time columns are in the "DATE" Format!
The Purpose is finally to get the number of students logged in each hour of the day,and finally draw a usage hour graph by school and time !
I have a spreadsheet with various date fields and next to those time fields. I need to calculate the amount of working hours between the 2 lots of values
for example
Cell A has "22/02/2008"
Cell B has "16:44:00"
Cell J has "25/02/2008"
Cell K has "08:59:00"
The answer i'm looking for is 01:29:00. The working hours are Monday to Friday 07:30 - 16:00. This is what I have so far and it gives me the total hours between the 2 dates but not the working hours. =IF(J10-A10=0,K10-B10,((J10-A10)+(K10-B10))) (Formatted with [h]:mm:ss). I have tried using workingday() but can't get the desired result
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.
So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to.
Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.
What formula should I use or if someone could create an example that would be really usefull :D
I have the output of my database in an excel file and one of the columns is the date of instalation (of a PC in this case) and I am trying to use the date of instalation to calculate the date of RENEWAL (we have a set life cycle for our machines and so we calculate in months when its due for an upgrade).
I think just a simple formula will work. I have attached an excel file with some example information.
In column B I have the computer type(this will determine how long of a life it has), In column D we have the instalation date which is the starting date (the start of its life cycle). Here are the variables.
HW/PC DESKTOP = 1/7/5
HW/PC DESKTOP CAD SP = 1/7/3
HW/LAPTOP = 1/7/3
HW/LAPTOP CAD SP = 1/7/3
HW/LAPTOP HIGH-END US = 1/7/3
HW/LAPTOP ULTRA-LIGHT FR = 1/7/3
And finally, in column F I would like to display the life cycle it has and then in column G have the exact date of renewal.
I know I have done this in the past, but now I am having trouble. I have dates in cells that I want to do the following:
First Scenario:
If the date is today, change the color of the text. I have:
Cell Value is equal to =TODAY()
that is working fine
Second Scenario:
If the date is anything within 4 days before today, I want that to change. I have:
Cell Value is Is Less than =Today()<4
that is not working
Third Scenario:
If the date has passed and less than 4 days before todays date, I want something to change. I have:
Cell Value is Less than =TODAY()-4
But that is not working.
[Code] ....
In column F I have a list of dates (dd/mm/yyyy).
I can manually filter dates ok, but want to set it to part of code to use automation for a report.
The code when run, finds nothing, where it should. I tried using [CDate] also but also same result.
I want a list of working dates for the year in excel. Wanted to have it so that there was some flexibility in the table too, but cannot get it to work 100%.
For some reason in the below example, in December, 26th shows up (despite being in the list of holidays) and the 29th (Sunday) shows up too.
[URL] .....
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View 9 Replies View RelatedI need to work out the number of working days between 2 dates, where the working week is Monday to Thursday, ignoring all bank holidays etc.
View 2 Replies View RelatedI have a job tracker program that daddylonglegs helped me with a few days ago. I thought I would be able figure this out on my own but failed. I've attached the file to help show what I need. I know the final ship date of a project. Sometimes my projects need to go out for teflon coating.
I need Networkdays to give me a TO TEFLON date that is 5 days before the final ship date and factor in weekends and holidays.
I need a simply excel formula or macro which calculates the numbers of months i need to accrue for certain costs. The data I have includes the start month of the invoice, the end month of the invoice (i.e. showing how long the invoice is relevant for, quite often 1 year), and obviously the current month.
(eg. a 12 month invoice with a start date of Oct 08, end date Sep 09, current May 09 = 8 mths to accrue)
(eg2. a 12 month invoice with a start date of Apr 09, end date of Mar 10, current May 09 = 2 mths to accrue)
(eg3. a 12 month invoice with a start date of Jun 09, end date of May 10, current May 09 = 0 mths to accrue)
I have a spreadsheet stating a date that my company will deliver a product by but due to a set of certain circumstances we need to pull forward the dates by 3 or 5 working days. Basically it needs to show the dates below but minus 3 days unless this includes non-working days (i.e. weekends)
01/01/10
02/01/10
03/01/10
04/01/10
05/01/10
06/01/10
07/01/10
08/01/10
09/01/10
10/01/10
I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm [code]....
I am trying to group some dates in a pivot table.
Unfortunately I receive an error saying it can't group the selection.
I have done "text to columns" and made the cells - (date values) and I have had no luck.
As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......
I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
View 9 Replies View RelatedI need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however
the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm
DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"
=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)
I have several text boxes on a 'picture' which is the format for a business review. The text boxes are linked to cells behind the picture which picks up company names, cities etc...
Two of the boxes have a phone# and date
The linked cells are formatted correctly but obviously the text boxes, pick up the 'values' not the formats.
is there any way for the textboxes to show the values with the proper format i.e.,
xxx-xxx-xxxx
mmm yyyy
I want to create a formula that works out the number of days between two dates but excludes weekends?
View 3 Replies View RelatedI have 2 dates+ times.
1 the contract start date and time.
2 the contract end date and time.
Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00
This is 2 part days and over a weekend.
I am attempting to format some TextBoxes from within a For/Next loop. I need a way to check which TextBox is the active TextBox in the loop. Using i as the variable, I came up with this code snippet: Me.Controls("TB" & i).Text = Format("TB" & i, "mm/dd/yy")
If i = 3, this gives me in TextBox3 (which is called TB3) the text 'TB3' and not the value of what is in TB3. It has got to bo something simple, I just can't see it!!!
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
