Calculating diff between date/ time only between certain hours 02-24-10

I am trying to calculate the difference in hours between a date/time range 
but only the hours in a certain range.

1/27/10 4:49 	1/27/10 11:32

I am trying to find the diff in hours only between 6:00 and 23:59, any hours 
that fall outside of that time frame should not be counted. Each calculation 
will have different dates so if there is a way to do this without specifying 
the date that would be great!

0
Utf
2/24/2010 5:01:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
832 Views

Similar Articles

[PageSpeed] 37

GMv1 -

If your start date is in A4 and your end date is in B4, then you can do this:

=MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1))

This works becase date/times are stored as numbers.  The date portion is the 
integer piece, and the time is stored as the decimal piece.  MOD pulls pulls 
the remainder after removing all multiples of 1 (which in this case returns 
the fraction piece of the date, which is the time).  So MOD(B4,1) is just the 
end time.

The start time portion just says if the time is less than 6AM  (.25 = 1/4 
day), then use .25 instead of the time (MOD(A4,1).   
-- 
Daryl S


"GMv1" wrote:

> I am trying to calculate the difference in hours between a date/time range 
> but only the hours in a certain range.
> 
> 1/27/10 4:49 	1/27/10 11:32
> 
> I am trying to find the diff in hours only between 6:00 and 23:59, any hours 
> that fall outside of that time frame should not be counted. Each calculation 
> will have different dates so if there is a way to do this without specifying 
> the date that would be great!
> 
0
Utf
2/24/2010 4:53:01 PM
Use:
=(INT(B1)-INT(A1))*18+(MOD(B1,1)-MAX(MOD(A1,1),TIME(6,0,0)))*24

Regards,
Fred

"GMv1" <GMv1@discussions.microsoft.com> wrote in message 
news:8A0F2954-B055-40F7-B2B4-5092149AE4BA@microsoft.com...
>I am trying to calculate the difference in hours between a date/time range
> but only the hours in a certain range.
>
> 1/27/10 4:49 1/27/10 11:32
>
> I am trying to find the diff in hours only between 6:00 and 23:59, any 
> hours
> that fall outside of that time frame should not be counted. Each 
> calculation
> will have different dates so if there is a way to do this without 
> specifying
> the date that would be great!
> 

0
Fred
2/24/2010 5:04:05 PM
Thank you this solution worked. I have another scenario that I have been 
unable to solve editing the formula you supplied.
1/27/10 4:49 1/27/10 11:32

I am trying to find the diff in hours only between 8:00 and 17:00 Monday to 
Friday only, any hours that fall outside of that time frame should not be 
counted. Each calculation will have different dates so if there is a way to 
do this without specifying the date that would be great! 

Thanks again for all of your help thus far!

"Daryl S" wrote:

> GMv1 -
> 
> If your start date is in A4 and your end date is in B4, then you can do this:
> 
> =MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1))
> 
> This works becase date/times are stored as numbers.  The date portion is the 
> integer piece, and the time is stored as the decimal piece.  MOD pulls pulls 
> the remainder after removing all multiples of 1 (which in this case returns 
> the fraction piece of the date, which is the time).  So MOD(B4,1) is just the 
> end time.
> 
> The start time portion just says if the time is less than 6AM  (.25 = 1/4 
> day), then use .25 instead of the time (MOD(A4,1).   
> -- 
> Daryl S
> 
> 
> "GMv1" wrote:
> 
> > I am trying to calculate the difference in hours between a date/time range 
> > but only the hours in a certain range.
> > 
> > 1/27/10 4:49 	1/27/10 11:32
> > 
> > I am trying to find the diff in hours only between 6:00 and 23:59, any hours 
> > that fall outside of that time frame should not be counted. Each calculation 
> > will have different dates so if there is a way to do this without specifying 
> > the date that would be great!
> > 
0
Utf
2/26/2010 3:17:01 AM
Hello,

I wrote a UDF for that:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd
0
Bernd
3/1/2010 3:29:40 PM
This worked perfectly. Thank you!

"Bernd P" wrote:

> Hello,
> 
> I wrote a UDF for that:
> http://sulprobil.com/html/count_hours.html
> 
> Regards,
> Bernd
> .
> 
0
Utf
3/2/2010 5:26:01 AM
Reply:

Similar Artilces:

NEWBIE question. Fields with calculations correct on form not showing up on report?
I have basic fields that are on a data entry form so when the clerk types in quantity and unit price the TOTALS field calculates this. When I look at or pull in the TOTALS field on my report it just shows $0's. How do I get the report to show what's on my form? On Thu, 24 Jan 2008 10:00:37 -0800 (PST), p-rat <osupratt@yahoo.com> wrote: >I have basic fields that are on a data entry form so when the clerk >types in quantity and unit price the TOTALS field calculates this. > >When I look at or pull in the TOTALS field on my report it just shows >$0's. How do I ...

Date formats
Hi. A text box bound to a DateField has yyyy\.mm\.dd as Format. Data is displayed correctly but while entering it (apparently) reverts to the computer date format, messing everybody's mind. Is there a way to keep entering and showing date format to yyyy\.mm \.dd? Thanks Henry You need to use an input mask. They are described in Acccess Help system. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "H. Martins" wrote: > Hi. > > A text box bound to a DateField has yyyy\.mm\.d...

Counting of certain hrs in to minutes
Dear Sir / Madam, :CONFUSED: THERE ARE CERTAIN QUERIES FROM MY END WHICH I WOULD LIKE TO GET SOLVED. I WOULD LIKE TO KNOW AS FOR 876000 HOURS HOW MANY MINUTES IT WOULD END UP IN 8760 HRS I.E. IN 365 DAYS OR A YEAR AND 8760 HRS HOW MANY MINUTES IT WOULD END IN 24 HRS THAT IS A DAY. Please let me know as soon as possible. Awaiting for your reply. -- iyer_parvati ------------------------------------------------------------------------ iyer_parvati's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29124 View this thread: http://www.excelforum.com/showthread.php?th...

Last Balanced Date
Does anyone know a quick way to get a list of accounts and the last date they were balanced? I'm finding myself having to select each account and manually check to see if it is up to date. I would think there would be a report of some kind that would display this somewhere, but I can't seem to find one. Any suggestions? Thanks in advance...Skip Skip, I've been thinking of the same exact question ever since I made the upgrade from Money 2004 to 2005. The ability to see the last balanced date across all accounts did exist in the 2004 version, and it was very useful. It...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

Calculate credit card APR
I am using excel 07. I would like to know how to setup a formula to calculate credit card apr or a pre done form that i can download. I have been searching but can not find exactly what i am looking for. I would like to enter current balance, apr and have it calculate what the interest is then have it minus payment and then recalculate balance and have it continue for the next month. I am not sure if this is enough information but i hope it is a start. Any information would help. Thanks in advance. Rob Rob wrote: > I am using excel 07. I would like to know how to setup a formula ...

Error when time should be 0:00
I have a timesheet that has several categories to put in time. The 1st Column has the total hours. Each of the other columns divides the time into hours and minutes worked on different projects and the last column assigns the remaining time. The problem is when all of the time has been assigned, I'm getting an error in the last column when it should display 0:00. Example: J26 L26 M26 N26 O26 P26 0:15 0:00 0:00 0:00 0:15 Should display 0:00 but i...

help populating appointment times in table
I will try to explain as simply as I can. I have a database that keeps client records and creates appointment dates and times after update of a provider name combobox by running a macro with several queries. Now here is where I don't know what I am doing. I have it working perfectly providing someone has not used the 9:00 spot in the appointment book because I have it defaulting to 9am. How do I add code to have the default change to a different time for any record where the 9:00 spot is already used? I am not very good with the code writing though I can manage to add a snippet in...

Calculate/Update Formulas
Lately, I have been having difficulties getting Excel to update existing formulas. I press F9 to update/calcuate and nothing happens. However, if I go to a particular cell with a formula, hit F2 to edit and simply press the enter key, the formula updates. What gives? Is this some type of virus? Thanks for any feedback. Hi could you post this specific formula? Also does hitting CTRL+ALT+F9 change the value -- Regards Frank Kabel Frankfurt, Germany Kevin wrote: > Lately, I have been having difficulties getting Excel to > update existing formulas. I press F9 to update/calcuat...

Payroll Calculator Excel Template
On the Payroll Calculator template under tax status, what do the numbers represent? Any suggestions on the template? ...

sequential date related question
Hi - If for example within cell A3 the date of 09/03/2001 was entered would it be possible in the cell beneath it, A4, to write code tha would break the date down into parts (e.g., Month, Date and Year) an to modify these parts individually? Therefore within cell A4 the code would suggest that it equaled A3 bu that it would have the potentail to modify its parts (e.g., Month, Dat and Year) and not as a whole (e.g., A3+365) Thus what I would like to place in cell A4 is code that would b somehow similar to the following =IF(MONTH(A3)<>12,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3)+1,1,1))...

date formatting changes
I have a VB macro that changes all date formats to dd/mm/yy. This works fine. However, sometimes when I go back into my files at a later date, I find that the dates convert back to dd/mm/yyyy. Why does this happen when I have specifically set the format to dd/mm/yy? There are some custom date formats that don't belong to you. I'm betting that you happened to use the same format that excel uses to tell it to pick up the format from the windows regional settings (under control panel). xl2002 is more honest with the way it deals with dates. (I think it's new with xl2002, but ma...

Daylight Savings Time Issue
I have an Exchange 2003 installation with 930+ mailboxes. I understand that I have to apply a patch to my Windows 2003 server and the Exahgne Patch on that server. I also know that I have to apply the patch for Windows XP. My Quesitons are: 1. Do I have to use the The TIme Zone Data Update Tool for Microsoft Office Outlook IF I use the Exchange Calendar Update Tool? 2. Will there be problems if I applied both patches to my server that also is the host for my Exchange installation and my clients are not updated or if they are updated. I just don't want to do double work if I don...

Web query time out problem
Hi, I have a workbook with around 350 sheets each with a web query in. If try to update all, about a half of the sheets will successfully updat before the message 'www..... The access to this internet site ha exceeded the allotted time'. Can anyone suggest a workaround? For example would it be possible t update the web queries in batches of 100? Thanks, Berti -- claytor ----------------------------------------------------------------------- claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1161 View this thread: http://www.excelforum.com/show...

Exchange 2000 Keeps Sending the same Mail each time the Server is Restarted.... !
Hi Folks; I'm running Exchange 2000 and I recently sent out email to 5,600 people that belong to my website. The problem is, each time I re-start the server it sends out all those emails - again. I've re-started the server 4 times now over the last few weeks (due to Windows Updates etc) and each time it sends the same emails to the same 5,600 people. Needless to say, people are starting to wonder about me ;-) Does anyone know what would cause Exchange 2000 to do this? Thanks! Marvin Hi, Look in the SMTP queue folder to see if the message is saved there. Leif "Marvin Mill...

MAIL 05-25-10
I am fine to mail? ...

PS07 Task Updates performed in Timephased View and actual hours mo
We use PS07 and update our tasks daily via the My Tasks page and the Timephased view. The task updates are typically accepted everyday and the projects are published via the publish feature on the 'Applied Task Update Requests and Errors' page. Then we're seeing that hours submitted one day are not there the next day in the resources 'My Tasks' view, or they have moved to other days and have weird hours. When the project plan is opened in Project Professional, the actual hours do not match what was updated and they do not match what PWA currently shows. ...

Problem Opening Certain Emails
I'm having this problem. When I try to open a certain email I receive the following error: This object was created in Outlook. This application is not available to open this object. Make sure the application is properly installed and that it has not been deleted, moved, or renamed. I Tried repairing office and applying a hotfix that Microsoft says it would solve it, but didn't work. If I try saving the email locally, I still get the above error when trying to open the file. But if you forward or save the email to another machine it opens fine. has anyone seen this probl...

Calculating age
I have a Spreadsheet that contains a column of dates (birthdays and anniversarys)in the dd/mm/yyyy format. Across row 1 I have the years 2010, 2011, 2012 etc. At the intersection of the two I wish to insert a formula that will calculate the age or the anniversary. I would be grateful for any help. Thanks in advance. See your other post. Regards, Fred "Chris waller" <Chriswaller@discussions.microsoft.com> wrote in message news:0AFDC0A0-1B1B-47DE-AD7B-E77B64E995AB@microsoft.com... >I have a Spreadsheet that contains a column of dates (birthdays and > an...

Date and time stamp
I would like to place a time and date in a cell that is only update when the cell that it is linked to is updated in any way. I have trie today() and now() but that updates anytime I do anything anywhere o the spreadsheet. I am using this as a log and as such I want to keep "time stamp" when someone enters their initials. Thanks, Troosh:confused -- Message posted from http://www.ExcelForum.com For a possible solution see http://www.mcgimpsey.com/excel/timestamp.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "troosh >&...

custom date format mmm/yy
Hi, is there any way in a Report to customize the date displayed in the report to be mmm/yy? Or even mmm/yyyy would be better for me than the options Access seems to offer in their reports. All I see there is Short Date, Medium Date, Long date - they all take up so much space in a densely packed report. Thanks, Harold You can use your own formats In a control's format property, enter mmm/yy or mmm/yyyy or mm/yy For a discusson of the formatting characters, put your cursor in the format property and press the F1 key for help. -- John Spencer Access MVP 2002-2005, 2007 Cent...

OLEobject properties at run time
Good afternon, my question is, how can I change the OLEobject properties of a textbox control at run time? any suggestions? ActiveSheet.OLEObjects("textbox1").Object.BackColor = &H8080FF is one way. User wrote: > > Good afternon, my question is, how can I change the OLEobject properties of > a textbox control at run time? > > any suggestions? -- Dave Peterson Thanks for responding, in my case I need to change the property "EnterKeyBehavior" to true, how make it? Besides, how make for viewing the diferent methods and properties that it ex...

filtering or sorting by sender time zone
I would like to optimize my opportunities for rapid two-way dialog by email. For example, when I read my email in Boston at 7:00 AM, I first respond to email from India, who are nearing the end of their day, then from Europe, who are about 6 hours ahead of me, and so on. Currently, I do this just by looking for specific senders and handling their email first. But, it would be great if I could write a filter that would sort email into GMT + 5.5, GMT+2, GMT, GMT-5, and GMT -8. Is this possible? Thanks, Rob ...

looking for free software to calculate composite reliability?
Hello, At your possible convenience, might anyone please kindly answer my question? Thank you very much. I need to calculate composite reliability for constructs (latent variables). I know some people calculate the composite reliability manually. I am wondering whether anyone might happen to know any free software (open resource) that can help calculate composite reliability? Thank you very much. Please take care Caroline Caroline, Post how the manual calc is done, and then we can help. I have no idea what you mean by composite reliability.... HTH, Bernie MS Excel MVP ...