Avatar

Please consider registering
Guest

Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

Register Lost password?
sp_Feed sp_topic_old
Help on Google Spreadsheet
Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
1
August 28, 2011 - 5:53 am
sp_Permalink sp_Print

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?

Avatar
Jim Hillier
Admin
Forum Posts: 2683
Member Since:
August 9, 2011
sp_UserOfflineSmall Offline
2
September 2, 2011 - 11:35 am
sp_Permalink sp_Print

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

Avatar
Mindblower
Montreal, Canada
Member
Forum Posts: 644
Member Since:
September 17, 2008
sp_UserOfflineSmall Offline
3
September 3, 2011 - 3:26 pm
sp_Permalink sp_Print

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"

Avatar
Carol Bratt
Mod
Forum Posts: 67
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
4
November 2, 2011 - 11:51 am
sp_Permalink sp_Print

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!

Avatar
Alan Wade
Sweden
Member
Forum Posts: 43
Member Since:
January 18, 2013
sp_UserOfflineSmall Offline
5
February 10, 2013 - 1:21 pm
sp_Permalink sp_Print

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

Most Users Ever Online: 2303

Currently Online:
21 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Member Stats:

Guest Posters: 11

Members: 3065

Moderators: 7

Admins: 4

Forum Stats:

Groups: 8

Forums: 20

Topics: 1915

Posts: 13434

Administrators: Jim Hillier, Richard Pedersen, David Hartsock, Marc Thomas

Moderators: Carol Bratt, dandl, Jason Shuffield, Jim Canfield, Terry Hollett, Stuart Berg, John Durso

ast-no-thumbnail">

Help on Google Spreadsheet

Avatar

Please consider registering
Guest

Search

— Forum Scope —






— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

Register Lost password?
sp_Feed sp_topic_old
Help on Google Spreadsheet
Avatar
raihan84
Member
Forum Posts: 12
Member Since:
March 4, 2011
sp_UserOfflineSmall Offline
1
August 28, 2011 - 5:53 am
sp_Permalink sp_Print

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?

Avatar
Jim Hillier
Admin
Forum Posts: 2683
Member Since:
August 9, 2011
sp_UserOfflineSmall Offline
2
September 2, 2011 - 11:35 am
sp_Permalink sp_Print

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

Avatar
Mindblower
Montreal, Canada
Member
Forum Posts: 644
Member Since:
September 17, 2008
sp_UserOfflineSmall Offline
3
September 3, 2011 - 3:26 pm
sp_Permalink sp_Print

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"

Avatar
Carol Bratt
Mod
Forum Posts: 67
Member Since:
August 11, 2011
sp_UserOfflineSmall Offline
4
November 2, 2011 - 11:51 am
sp_Permalink sp_Print

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!

Avatar
Alan Wade
Sweden
Member
Forum Posts: 43
Member Since:
January 18, 2013
sp_UserOfflineSmall Offline
5
February 10, 2013 - 1:21 pm
sp_Permalink sp_Print

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

Most Users Ever Online: 2303

Currently Online:
21 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Member Stats:

Guest Posters: 11

Members: 3065

Moderators: 7

Admins: 4

Forum Stats:

Groups: 8

Forums: 20

Topics: 1915

Posts: 13434

Administrators: Jim Hillier, Richard Pedersen, David Hartsock, Marc Thomas

Moderators: Carol Bratt, dandl, Jason Shuffield, Jim Canfield, Terry Hollett, Stuart Berg, John Durso

Scroll to Top

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!