I have formatted a cell as a preset custom Time format of h:mm
When I type in the number 2 into the cell, I want it to show as the number of hours: 2:00
When I do this, the cell remains blank: 00:00 and I see the following in the function line:
1/2/1900 12:00:00 AM
It seems to be reading the "2" as the month rather than the number of hours.
I have tried a lot of different options but can't get this to work -- and it should be so simple.
Any idea on how I can get this to work?
I have cells that are custom formatted to Ddd Mmm D h:mm:ss Yyyy
and they read Fri Apr 1 09:36:42 2011
there are two columns, one with start (A) and one with end (B)
I NEED TO CREATE A COLUMNS THAT CALCULATES THE ELAPSED TIME ONLY (THE HOURS MINUTES SECONDS)
I HAVE NOT BEEN ABLE TO FIND A FORMULA THAT GIVES ME ONLY THE TIME ELAPSE
I have lots of dates on a page that have been imported into excel, they look like this:
14 Jan 2009 21:00
I want a piece of code that will remove the time from the date in every column but also excel doesnt recognise 14 Jan 2009 as a date so cannot be formatted. Is there any way to get excel to remove the time and recognise the date as being a date like 14-Jan-2009.
Any help is appreciated.
Thanks
Joz
I have been reading the posts here and have made great progress. Getting the correct elapsed time to display will either work for instances under 24 hours or orver, but I can't get both to happen with the same formula.
Currently using this:
=INT(b2-a2) & " days " & TEXT(MOD(b2-a2,1),"[h]:mm:ss")
I cannot change the date system to 1904, so I'm getting a negative number for the days. Additionally, for time that has elapsed that is less then 24 hours, the days value is still -1 (rather then 0). Time appears to be calculating fine. Time elapsed over 24 hours, the days seem to be correct, but a negative value.
Does anyone have a suggestion for getting the days to calculate properly? I have attached a sample of the data I'm using.
I need to calculate the total overall time a person worked. This may overlap
two or three days.
i need to make sure that they get at least an 8 hour break between work
periods also.
Is there a calcuation that would do that for me?
Thanks in advance
--
bob
I have four cells: E1=date 1, E2=date 2, G1=time1, and G2=time2. Cells E1 and G1 correspond with each other to make up a complete date and time. I want to calculate the number of hours between the date and time received (E1&G1) and the date work began with items received (E2&G2). How to I set up my calculation to allw me to determine the number of hours when the date and time fields are all separate?
Also I want to be able to enter the time data as 1536 for 15:36 and have the entered result convert to "15:36" with the colon in the middle. How do I do that?
Thanks
Hi there
I have 4 columns
a) Date of collection dd/mm/yy
b) Time of collection hh/mm
c) Date of delivery dd/mm/yy
d) Time of delivery hh/mm
I have been asked to calculate the delivery time in hours / minutes. Could somebody provide me with a formula or method to deduct c and d from a and b.
TIA
Hippo
Could any body advise the formula req to deduct 15Hrs from 10/12/2004 06:00
giving the result of 09/12/2004 15:00 many thanks
--
Tony
Help! please, (be gentle i'm a new user!!)
I'm trying to calculate the difference in hours and mins between two dates and times (UK date format)e.g
29/01/2009 06:31 to 28/02/2009 21:39
I have three cells 1st & 3rd formatted as
ddd dd mmm hh:mm
The second formatted as [hh]:mm (I do not understand the relevance of [ ]
the first cell reads Sun 27 Jul 20:00
the second cell reads 01:33
the third cell has a formula A1 + A2 with the resultant display of
Sun 27 Jul 21:33
However, if I change the formula to A1 - A2 all I get is ##############
I need to not only add time, but to subtract time.
Suggestions please.
Regards Philip
