05-10-2006, 20:03
|
#16
|
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
|
|
|
05-10-2006, 20:11
|
#17
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
Re: Another Excel question!
Nest another IF dependent on whether A2 is 0/blank...
|
|
|
05-10-2006, 20:33
|
#18
|
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....
|
|
|
05-10-2006, 21:07
|
#19
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
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...
|
|
|
05-10-2006, 22:42
|
#20
|
Inactive
Join Date: Nov 2003
Location: Surrey
Posts: 1,356
|
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)
|
|
|
05-10-2006, 22:47
|
#21
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
Re: Another Excel question!
Quote:
Originally Posted by fireman328
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?
|
|
|
06-10-2006, 06:36
|
#22
|
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
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!!
|
|
|
06-10-2006, 06:47
|
#23
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Quote:
Originally Posted by lsainsbury
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?
|
|
|
09-10-2006, 12:51
|
#24
|
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???
|
|
|
09-10-2006, 12:55
|
#25
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
Re: Another Excel question!
Quote:
Originally Posted by lsainsbury
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...
|
|
|
09-10-2006, 13:27
|
#26
|
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!
|
|
|
09-10-2006, 14:15
|
#27
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Quote:
Originally Posted by lsainsbury
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!!
|
|
|
09-10-2006, 14:51
|
#28
|
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...
|
|
|
18-10-2006, 11:45
|
#29
|
Inactive
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
|
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.
|
|
|
18-10-2006, 12:07
|
#30
|
Inactive
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
|
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,
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT +1. The time now is 12:13.
|