date and time calculations

What would my formula and format of cells be for calculating hours between dates and times.  
Ex. start time Jan 2, 04 8 am to end time Jan 4, 04 5 pm.  I DO NOT want my times or answers in military time.
If you could please give me an example of how to format each cell and the formula for each cell, it would be greatly appreciated.  Thank you.
0
anonymous (74722)
1/22/2004 12:16:10 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
265 Views

Similar Articles

[PageSpeed] 39

Matilda

In the UK I'd enter the start time as 2/1/04 8:
Excel will format this as 02/01/04 8:00 or whatever your 
default setting is. Notice the space between the date and 
the start time.

The formula for calulating the hours is
=IF(I8<I7,(I8+0.5)-I7) where I7 is the start and I8 is the 
end. You will need to format the cell to time.

If people work night shift the formula will be more 
complex.

Regards
Peter
>-----Original Message-----
>What would my formula and format of cells be for 
calculating hours between dates and times.  
>Ex. start time Jan 2, 04 8 am to end time Jan 4, 04 5 
pm.  I DO NOT want my times or answers in military time.
>If you could please give me an example of how to format 
each cell and the formula for each cell, it would be 
greatly appreciated.  Thank you.
>.
>
0
1/22/2004 4:04:20 AM
Reply:

Similar Artilces:

email delievere dmultiple times
i have an Exchange 2003 FE-BE topology setup. i have one user who is receiving a single message repeatedly at various time intervals. i seem to remeber reading about this condition some time ago but can no longer fnid any reference to the issue in KB articles. anyone have any advice where to look? Lots of reasons for this, but if it's an external message, look in the SMTP protocol logs and see if the sending server is correctly ending the conversation. If the sending server doesn't believe the delivery was successful, it will try again. We often see this with bad firewall conf...

Formulas for race game (position calculation after each round)
Hi. I have a worksheet like the following: Player's Name-----Round1(R1)-----R2-----R3...-----Total Score====Position after R1-----Pos.(R2)-----Pos.(R3)... Position is calculated based on the score accumalted (the more scores, the better) so far. Eg: Postion after R1 depends on score got in R1. Position after R2 depends on score got in R1 & R2. Position after R3 depends on score got in R1 & R2 & R3. Q: What formulas should I use to calculate the positions up to different rounds? I think I can use RANK functions, but it seems I need to set SUM formulas to calculate total scores...

Dates in Formulae
Dear all, Suppose I want to create a basic formula eg one which adds 6 months to a date, how would I do it? Doing it by date + 6 months worth of days will obviously not work as the number of days in 6 months will vary. Any thoughts? Thanks, Danny Hi Danny, =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) But do think about what you want the day to be in case the source date is, for example, august 31. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Aardvark" <djurmann@hotmail.com> wrote in message news:%23cU8IWF0DHA.2580@TK2MSFTNGP09.phx.gbl... > Dear all, > > ...

Calculated Control Refers to Multiple Reports
I have five reports, which for formatting reasons cannot be combined into one. They all open automatically with one command and need to be paginated as if they are one report. The number of pages for each report will differ depending on the data entered for each record. Pagination for each report sums the total number of pages ([Pages]) for the previous reports. The calculated control in the reports looks like this: report name: Report 2 control name: NewPage control source: =[Reports]![Report1]![Pages]+1 report name: Report 3 control name: NewPage control source: =[Reports]![Report1...

Auto fill cells with dates, exclude weekends?
What I'm doing is filling in multiple cells with dates (by dragging and filling) automatically. I was wondering if there was anyway for it to skip the weekends within the dates? Thanks in advance. Hi Sam rather than dragging with the left mouse button down, drag with the right .... when you let go you'll see an option for fill weekdays. Cheers JulieD "Sam Weber" <sam@hostradius.com> wrote in message news:af3901c4796d$85dff660$a501280a@phx.gbl... > What I'm doing is filling in multiple cells with dates > (by dragging and filling) automatically. I was wo...

Formula for calculating Gross Profit with muktiple discounts #2
List Price less Disc1 less Disc2 equals Net Price. Sell price less net price equal profit divided by sell price equal gross margin percentage. $10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less $4.50 =$1.50. $1.50/6.00 = 25% GP. All of these figures are in cells of their own. Cannot get this to calculate correcly. Please help! Urgent CARM Wrote: > List Price less Disc1 less Disc2 equals Net Price. Sell price less net > price equal profit divided by sell price equal gross margi > percentage. > > $10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less > $4....

Converting a Date
I have a column with dates in the format mm/dd/yyyy. I would like to have an additional column the will give me only the month and another only the year, all from the above date column Pls Advise. Thankyou. Do not store the month and year in other columns. Instead extract the month and years using the Month() and Year() functions. They will give you number of both. For example today (March 19, 2010) will show up as 3 for the month. If you need to see March instead of 3, use the Format Function something like below: Format([TheDateColumn], "MMMM") -- Jerry Whi...

Why does a graphic or print multiple times in the same document?
When I print from publisher to my Canon color copier,graphics appear multiple times in the same document. This could be many things- Temp files need cleaning A simple restart of cpu & printer A bad printer driver Check those first. "Carla" wrote: > When I print from publisher to my Canon color copier,graphics appear multiple > times in the same document. Carla wrote: || When I print from publisher to my Canon color copier,graphics appear || multiple times in the same document. Carla, check out the following kb article. This is actually a known bug that affects the e...

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! ...

Contract Date Issue
Basically, you create a contract normally. However, when you go to add a contract line, you get a date error. Apparently, the system thinks that the contract line start and/or end dates are outside the contract start and end dates, even though the contract line defaults to the using the contract start and end dates (i.e. the dates on the line match those on the contract). I know this must be a data type issue with the datetime values. Has anyone else had this problem? Is there a fix? On Mon, 31 Oct 2005 15:08:14 -0500, "Tim Powers" <tim.powers@rimrock.com> wrote: ...

Activity without date
In my current project plan I know a few activities that will be conducted, more specifically meetings that are important. Now, I don't know the date they will happen yet and I want to indicate that by adding the activity but not entering a date. Is that allowed in MS Project? Since Project is designed to calculate dates, not only can you enter a task without a date, you should be entering ALL tasks without dates and then Project tells you when you should schedule them. -- Steve House MS Project Trainer & Consultant "Hertsh" <Hertsh@discussions.microsoft....

Archiving broke
Outlook is not archiving most of my deleted and sent items. Yes, I've read about making sure the modification date is within range as well as making sure these folders are set to archive. THE SETTINGS ARE CORRECT. I heard somewhere that if the msgs modification date is "none", then Outlook interprets this a 1/1/4501 (and empty date) and therefore things don't get archived (my deleted and sent items). Anyone heard if this is true...and if so, is there a workaround?????? Amil Why are you keeping deleted items? If you need to keep them they should be in a specific Folder...

Excel importing dates from a RTF in a macro
If I open a RTF with a date in, the date appears as it does in the original RTF. If however, if I run Office 2000 and record the above event using a macro, when played the date swaps the mm and dd round (without changing the properties, so in fact the mm is now the dd and the dd is now the mm) This only occurs if the dd is 12 or below in value for obvious reasons. I then tried to open the RTF date column as text, and then change the column properties after it is imported. This does work fine, but I am using the Application.Filesearch and am not sure how to integrate the OpenText function whic...

Combining date and time into one cell
I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How can I merge these two in one cell with the format m/dd/yy hh:mm ? Thanks. Date in A1, time in B1, combined in C1: formula is =A1+B1 and format as you describe. On Sat, 22 Jan 2005 14:03:02 -0800, "Kelly C" <KellyC@discussions.microsoft.com> wrote: >I have the date (m/dd/yy) in one cell and time (hh:mm) in another cell. How >can I merge these two in one cell with the format m/dd/yy hh:mm ? > >Thanks. =TEXT(A1,"m/dd/yy ")&TEXT(B1,"hh:mm") is one way -- HT...

Using SUMIF to add data between a range of dates
Hi, I am developing a cashflow spreadsheet, and need to add a range of values (in column B) based on the criteria that they are relating to a set week, ie in column B has the amount to be paid, and column C has the date the amount is due. I need to find out the total amount due between 2 dates. Does anyone know how I can do this? Hi With start date in B20 and end date in B21 try this: =SUMIF(C2:C5,">=" &B20,B2:B5)-SUMIF(C2:C5,"<="&B21,B2:B5) Regards, Per "Jaspa" <Jaspa@discussions.microsoft.com> skrev i meddelelsen news...

International date formats
When performing an Edit, replace on the cell value 10.09.03 (a system genereated date vale of 10th Sep 03) replaceing the . with /, Excel (and in VBA when recorded as a macro) sometimes changes the cell value to 09/10/03 (9th October 03). This is driving me potty and have spent a vast amount of time on it trying to work out what Excel is doing!!!! My Regional settings are set fro the UK. Any ideas anyone? "Mike" <michael.may@npower.com> wrote in message news:2d44701c39483$97f77730$a601280a@phx.gbl... > When performing an Edit, replace on the cell value > 10.09.0...

Calendar entries and time zones
Hi! I have the following problem with Outlook: I moved from Europe to US, and what I did was just to change the computer clock (but not the time zone). Now the time Outlook writes in email headers is always offset by 6 hours. To change that, I wanted to change the time zone to the correct US zone instead of the European zone. But then I have the problem that all the calendar entries are shifted automatically by 6 hours which is definitely NOT what I want. Does anybody know how to solve that problem? How can I change the time zone for my computer without having to change back all the calendar e...

how to send mails at a scheduled time
hi, does anyone know how to send mails at a scheduled time from outllok express or microsoft outlook thanks depending on your version of outlook, there might be two ways, but in Outlook 2000 and above, with the email you're going to send open, go to View, Options...there's a checkbox that reads "Do not deliver before" and you can select the date and time you want the message sent... "CARETTA" <mail@mail.com> wrote in message news:OXJ1OZsqDHA.2268@TK2MSFTNGP12.phx.gbl... > hi, > does anyone know how to send mails at a scheduled time from outllok exp...

Active Sync timeed out with DEV_10
I have problems with sync my mobile. normally the mobile syncs 100 Objects and then stops. Next time it syncs the next 100 objects and stops again. Does anybody know why just one hundred !? On the Device I got the following error (at the end of the log) =-=- [15/3/2006 9:26:8.0] -=-= =-=-=-= Server Response =-=-=- No Header Found, got error: 80070006 Thanks Marc ...

entering current time in shared work book
Hi, i am maintaing a excel tracker in shared drive where i am manually entering the time when a person came into the office. Is it possible that when a person comes he himself open the shared file and click the cell corresponding to his name so that the time of that moment can be stored and that can not be further updated with new value (time)... below is the format Name of Person Date Name A Name B Name C 21-Apr-10 6:44 PM 6:48 PM 6:40 PM 22-Apr-10 6:30 PM 6:34 PM 6:31 PM 23-Apr-10 6:35 PM 6:39 PM 6:40 PM I would try first to setup a list on another she...

Using AVERAGEIFS to calculate average rating for programs-Reposted (was unclear)
Here is an example for the Rating database: Date Start Time End Time Channel 1 Channel 2 Channel 3 Channel 4 Channel 5 Channel 6 Channel 7 Channel 8 Channel 9 Channel 10 1/2/2010 06:00 06:15 0 0 0 0 0 0 0 0 0 0 1/2/2010 06:15 06:30 0 0 0 0 0 0 0 0 0 0 1/2/2010 06:30 06:45 0.1 0.1 0.1 0.1 0.1 0 0.1 0.2 0.2 0.1 1/2/2010 06:45 07:00 0.2 0.2 0.1 0.2 0.2 0.1 0.2 0.1 0.1 0.1 1/2/2010 07:00 07:15 0.2 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.1 1/2/2010 07:15 07:30 2.5 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.3 1/2/2010 07:30 07:45 2 0.1 0.2 0.2 0.2 0.2 0 0 0.1 0.2 1/2/2010 07:45 08:00 3 4 0.2 4 0...

downloaded transaction date
There used to be a setting in prior versions of Money to not overwrite your money file with the downloaded transaction dates when matching transactions. Does anybody know how to make that change in Money 2005? I've not had any luck finding it. Thanks. -z I found the setting - tools/settings/online services/uncheck the 'overwrite transactions' box. Cheers, -z "zender" <anonymous@aol.com> wrote in message news:uelFWDVuEHA.2788@TK2MSFTNGP09.phx.gbl... > There used to be a setting in prior versions of Money to not overwrite > your money file with t...

Automatic date editing
Hi When I edit a cell, I would like to know if the last editing date could be shown automaticly next to it example ID Value Last date value was edite Refrigerator $945.00 12/1/200 Thanks... Hi Marcio You can do it with the change event of the worksheet This example will place the date/time in the B column if you change a cell in the range A1:A20. Place the code in the Sheet module Right click on a sheet tab and choose view code Paste the code there Alt-Q to go back to Excel Private Sub Worksheet_Change(ByVal Tar...

Excel Re-calculation 2000 compared to 2003
My company has recently deployed MS Office 2003. The previously deployed version was Office 2000. One of our users has an 11MB financial workbook that contains lots of formula. Mostly of IF, SUMIF and SUM calculations. There are plenty of links between the 25 worksheets within the one workbook but no links to other workbooks. Entering data into the workbook using Excel 2000 is nearly instantaneous. However entering data into Excel 2003 takes about 15 seconds each time I press enter (or direction key) as the workbook recalculates each time. The settings for calculation are identical with cal...