This forum requires Javascript to be enabled for posting content
Log In
Please consider registering
Guest
Forum Scope


Match



Forum Options



Min search length: 3 characters / Max search length: 84 characters
Register Lost password?
Help on Google Spreadsheet
raihan84
12 Posts
(Offline)
1
August 28, 2011 - 5:53 am

Hello guys, I am back with a problem. It is on google spreadsheet.

Say you have got 3 cells named A, B and C

A = In time
B= Out time
C = B-A

for google spreadsheet A and B cells must be filled as the format [b:ulutni3f]hh:mm:ss am/pm[/b:ulutni3f]. Now whenever the sum of those hours become 25 (say) then the result shows 1:00:00. But I want to show the result as 25:00:00.

How can I do that?

Jim Hillier
2700 Posts
(Offline)
2
September 2, 2011 - 11:35 am

Hi Raihan - Sincere apologies for the lack of any reply here mate. We haven't been ignoring you, our resident expert in all things 'Office', the lovely Carol, is currently indisposed.

If you have found an answer to this issue please let us know the solution. Otherwise please hang tight and Carol will be back on board with the right advice shortly.

Thanks......Jim

Mindblower
Montreal, Canada
663 Posts
(Offline)
3
September 3, 2011 - 3:26 pm

A silly question here raihan84, [quote:1hf0u71r]But I want to show the result as 25:00:00[/quote:1hf0u71r]

Is this all you need / want, since an @IF command could clear this problem for you (at least in Lotus), Mindblower!

"For the needy, not the greedy"

Carol Bratt
67 Posts
(Offline)
4
November 2, 2011 - 11:51 am

Ok, I apologize profusely for my absence and making you wait for a reply. I am truly sorry but due to circumstances beyond my control I and family obligations I was delayed in replying to this query. Hopefully I will make up for it by giving you good advice.

Ok, here we go:

First, if you want to use real time values in your calculations you need to know the "magic" numbers to keep in mind. These are:

60 (Sixty Minutes)
60 (Sixty Seconds)
3600 (60 Secs * 60 Mins)
24 (Twenty Four Hours)
1440 ( 60 Mins * 24 Hours)
86400 (25 Hours * 60 Mins * 60 Secs)

Now that you know these numbers and the information above you should no longer have problems with the manipulation of time and dates in Excel. You must be certain that your times are valid though.

To enter a time that is based on the 12-hour clock, type a space, and then type "a" or "p" after the time; for example, 9:00 p. Otherwise, Excel enters the time as AM. To Enter a time based on a 24-hour clock (military time) enter as 21:00, 11:00 etc.

To enter the current time, press CTRL+SHIFT+; (semicolon).

[b:17cs4ccz]Adding/Subtracting Excel Dates & Times.[/b:17cs4ccz]
As Excel Dates & Times are seen as Serial Values (Dates) and Decimal Fractions (Times) we can easily add or subtract them like below;

=A1-A2

=A1+A2 or =SUM(A1:A2) See Also: Adding Excel Times Past 24 Hours

When subtracting dates we naturally want to subtract the lesser date from the greater date. If you are unsure which cell will house which you can use;

=MAX(A1:A2)-Min(A1:A2)

[b:17cs4ccz]Subtracting Times That Span Past Midnight Into a New Day[/b:17cs4ccz]

Let's say A1 hasthe Start time of 6:00PM and A2 the end time of 2:00AM. If we need to work out the hours worked between 6:00PM of 1 day and 2:00AM of the next day we cannot simply use;

=A2-A1

As Excel does not like negative times

Instead, we should use;

=A2+(A1>A2)-A1

This will then add 1 (1 day) to A2 if the time in A1 is of a higher value than the time in A2. The expression (A1>A2) will equate to either TRUE or FALSE. TRUE has a value of 1 and FALSE a value of zero.

Add/Subtract Days to a Date

To add/subtract, say 2 days to a date, we can simply use;

=A1+2

=A1-2

Calculate the Difference Between 2 Dates

To find out the difference in 2 dates use:

=DATEDIF(A1,A2,"d")

Where A1 houses the earliest date. This will result in the number of days between 2 dates.

If, when working with dates and times we cannot know in advance which date or time is the earliest we can use the MIN and MAX functions, for example we could use:

=DATEDIF(MIN(A1:A2),MAX(A1:A2),"d")

The syntax for the DATEDIF function is;

DATEDIF(Start _Date,End_Date,Unit)

Valid Units are any one of the formats below

"M", "D", "Y", "YM" (The months and years of both dates are ignored) ,"YD" (The days and years of both dates are ignored) and "MD" (The years of both dates are ignored).

Convert From Decimal Time

If you have the number 5.50 and you really want 5:30 or 5:30AM use:

=A1/24 and format as needed.

If it should be 17:00 or 5:30PM use:

=(A1/24)+0.5

Convert To Decimal Time

To get the opposite, that is a decimal time from a true time, use

=A1*24

Extract Date Only From Date & Time

If a cell has both the true date and true time (e.g. 22/Jan/02 15:36) and we only want the Date, use:

=INT(A1)

Extract Time Only From Date & Time

To get the time only use:

=MOD(A1,1) and format as needed.

Negative Times

By default, excel cannot calculate or show true negative times, instead you will see ############. This can be overcome by switching Excel to the 1904 Date System via Tools>Options - Calculations and check the 1904 Date System checkbox. The 1904 date system changes the Starting date from which all dates are calculated from January 1, 1900, to January 2, 1904. This is VERY IMPORTANT as, if you are using other Date calculations in the same Workbook they will end up returning erroneous results.

Add/Subtract Months or Years to a Date

We can also easily tell Excel to add to any date any amount of days, months or years. Here is how:

=DATE(YEAR(A1)+value1,MONTH(A1)+value2,DAY(A1)+value3)

So to add 1 month to a date in cell A1 we could use:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

to add 1 year to a date in cell A1 we could use:

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

There are however some other Date and Time functions Excel has that are part of the Analysis ToolPak Click Add-Ins on the Tools menu. Click to select the Analysis ToolPak check box, and then click Yes if you are asked if you want to install it.

You will then have functions such as:

EDATE

EOMONTH

NETWORKDAYS

WEEKNUM

All of these will be found under the Date & Time category of the Paste Function dialog, Function Wizard.

I hope you find this information useful!

Alan Wade
Sweden
43 Posts
(Offline)
5
February 10, 2013 - 1:21 pm

I know this is an old topic but should anyone else want hours in an Excel work sheet to total past 23 then just format the cell as such [hh]:mm:ss;;;@

With that the hours will add up past one day e.g. 25 etc but the minutes and seconds wont.

Forum Timezone: America/Indiana/Indianapolis
All RSSShow Stats
Administrators:
Jim Hillier
Richard Pedersen
David Hartsock
Moderators:
Carol Bratt
dandl
Jason Shuffield
Jim Canfield
Terry Hollett
Stuart Berg
John Durso
Top Posters:
Chad Johnson: 867
Mindblower: 663
carbonterry2: 356
Flying Dutchman: 278
grr: 211
Newest Members:
mashley
emmaagro
Sponsor
knowledgekit
davidwiese18
Forum Stats:
Groups: 8
Forums: 20
Topics: 1939
Posts: 13514

 

Member Stats:
Guest Posters: 11
Members: 3170
Moderators: 7
Admins: 3
Most Users Ever Online: 2303
Currently Online:
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Exit mobile version

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!