Home News Forum Articles
  Welcome back Join CF
You are here You are here: Home | Forum | Another Excel question!

You are currently viewing our boards as a guest which gives you limited access to view most of the discussions, articles and other free features. By joining our Virgin Media community you will have full access to all discussions, be able to view and post threads, communicate privately with other members (PM), respond to polls, upload your own images/photos, and access many other special features. Registration is fast, simple and absolutely free so please join our community today.


Welcome to Cable Forum
Go Back   Cable Forum > Computers & IT > General IT Discussion

Another Excel question!
Reply
 
Thread Tools
Old 05-10-2006, 21:03   #16
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Arrrghhhhh...Just one small issue....

When no times are entered, the total says "7:30"
Anyway of getting that to report 0?

Cheers

Lee
  Reply With Quote
Advertisement
Old 05-10-2006, 21:11   #17
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Nest another IF dependent on whether A2 is 0/blank...
Cobbydaler is offline   Reply With Quote
Old 05-10-2006, 21:33   #18
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Grrrr - not sure I've done it right....
  Reply With Quote
Old 05-10-2006, 22:07   #19
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Code:
=IF(A2<>0,IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30")),0)
Try that...
Cobbydaler is offline   Reply With Quote
Old 05-10-2006, 23:42   #20
fireman328
Inactive
 
fireman328's Avatar
 
Join Date: Nov 2003
Location: Surrey
Posts: 1,356
fireman328 has reached the bronze age
fireman328 has reached the bronze agefireman328 has reached the bronze age
Re: Another Excel question!

My brain is beginning to hurt, I think I have opened a can of worms. I shall have to give her a severe talking to,
for bringing it up !!!!! (Double entendres intended)
fireman328 is offline   Reply With Quote
Old 05-10-2006, 23:47   #21
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Quote:
Originally Posted by fireman328 View Post
My brain is beginning to hurt, I think I have opened a can of worms. I shall have to give her a severe talking to,
for bringing it up !!!!! (Double entendres intended)
Ummm... Perhaps the wrong thread Fireman?
Cobbydaler is offline   Reply With Quote
Old 06-10-2006, 07:36   #22
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Quote:
Originally Posted by Cobbydaler View Post
Code:
=IF(A2<>0,IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30")),0)
Try that...
That changes it to 00:00 but when times are entered it brings up a load of hashes!!

My brain hurts too!!
  Reply With Quote
Old 06-10-2006, 07:47   #23
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
Re: Another Excel question!

Quote:
Originally Posted by lsainsbury View Post
Arrrghhhhh...Just one small issue....

When no times are entered, the total says "7:30"
Anyway of getting that to report 0?

Cheers

Lee
OK, this will say "Blank" if the start time cell is empty:

=IF(ISBLANK(A2),"Blank",IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30")))

Replace "Blank" with "" or 0, or whatever you like.

Edit: Cobbydaler's formula works for me - did you paste it into the correct place? And change the cell format to hh:mm?
TheNorm is offline   Reply With Quote
Old 09-10-2006, 13:51   #24
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Still having a few issues with this - is it becuase 24hr clock and AM / PM has been mixed in the formula???
  Reply With Quote
Old 09-10-2006, 13:55   #25
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Quote:
Originally Posted by lsainsbury View Post
Still having a few issues with this - is it becuase 24hr clock and AM / PM has been mixed in the formula???
The times entered will all have to be in 24 hr format...

The cells your entering into will have to be formatted hh:mm...
Cobbydaler is offline   Reply With Quote
Old 09-10-2006, 14:27   #26
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

Yep - done that - made sure all the cells are hh:mm.

Just done a bit of mucking about:

Quote:
=IF(ISBLANK(E10),"None",IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30")))

...works a treat!

Thanks to everybody who helped in the formula!
  Reply With Quote
Old 09-10-2006, 15:15   #27
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
Re: Another Excel question!

Quote:
Originally Posted by lsainsbury View Post
Yep ...Just done a bit of mucking about:
...
Well, I can't see what you've changed apart from the cell references, but glad to see that you are happy! Excel can be both wonderful and wonderfully frustrating!!
TheNorm is offline   Reply With Quote
Old 09-10-2006, 15:51   #28
LSainsbury
Guest
 
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT, Mobile: Orange, Internet: Quite Slow!
Posts: n/a
Re: Another Excel question!

I think it was something to do with my cell formats....I blasted them and re-did them...all now ok...
  Reply With Quote
Old 18-10-2006, 12:45   #29
liamboyle06
Inactive
 
liamboyle06's Avatar
 
Join Date: Apr 2005
Location: St. Johnston, Co Donegal, Ireland
Age: 44
Services: 3 Mobile Sky+ HD NWE 2MB Wireless Broadband XBOX 360 PS3 Wii
Posts: 550
liamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of lightliamboyle06 is a glorious beacon of light
Send a message via MSN to liamboyle06
Re: Another Excel question!

If you do not mind me hi-jacking this thread, I have another Excel question.

I have lists of cells each containing a date and Time (dd/mm/yyyy HH:mm:ss), I can format the cells to show the dates of the months only (1 February 2006 etc), but when I pivot the table to get a count of the number of dates, it includes the time also.
Is there any way around this.

I have tried to format the pivot table for the dates only, but does not give correct results, i tried using ASAP to delete all characters after the 5th, still will not work - any ideas any one.

I have attached an example to show.
liamboyle06 is offline   Reply With Quote
Old 18-10-2006, 13:07   #30
gazzae
Inactive
 
gazzae's Avatar
 
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
Re: Another Excel question!

Don't use pivot tables much myself so there may be an easier way.

But if you add another column use the following formula to grab just the date...

=TEXT(B2,"dddd, mmmm dd, yyyy")

and then do the count on this column,
gazzae is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 10:26.


Server: osmium.zmnt.uk
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.